Prev: Need help with the Query.
Next: Reference integrity between two databases
From: Mark McGinty on 25 Feb 2010 21:14
Greets,

A company I work for has granted ODBC access to some of their clients (a
risky proposition, I totally agree.) I have been tasked with analyzing the
load these clients incur, and in the course of that, I noticed some calls to
sp_table_statistics2_rowset were taking upwards of 100 seconds to return!
That seems excessive so I'm considering restricting permissions -- does
anyone know what [if anything] that will break?

I also noticed the application was "Microsoft SQL Server", does that mean
these calls have something to do with linked servers?

I did search MSDN, 'bing' returned 2 links to the same article, which listed
this SP as being documented in BOL... sadly, it is not. (When is MS going
to just concede the search thing and contract Google to do it right?) :-)


TIA,
-Mark


From: Mark McGinty on 25 Feb 2010 21:38

"Mark McGinty" <mmcgintyNG(a)sSpUaCmKSdeprecatethis.com> wrote in message
news:OMmv8lotKHA.4860(a)TK2MSFTNGP05.phx.gbl...
> Greets,
>
> A company I work for has granted ODBC access to some of their clients (a
> risky proposition, I totally agree.) I have been tasked with analyzing
> the load these clients incur, and in the course of that, I noticed some
> calls to sp_table_statistics2_rowset were taking upwards of 100 seconds to
> return! That seems excessive so I'm considering restricting permissions --
> does anyone know what [if anything] that will break?
>
> I also noticed the application was "Microsoft SQL Server", does that mean
> these calls have something to do with linked servers?
>
> I did search MSDN, 'bing' returned 2 links to the same article, which
> listed this SP as being documented in BOL... sadly, it is not. (When is
> MS going to just concede the search thing and contract Google to do it
> right?) :-)

OIC, they're opening a cursor and iterating through it, so duration is
likely skewed some... I still don't like it though! :-)

-Mark


From: Michael Coles on 25 Feb 2010 22:20
That's a system stored proc that's used by client connectivity APIs. If you
disabled permissions or something like that you'd probably have a hard time
connecting to your server using some client APIs (presumably ODBC in this
instance).

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Mark McGinty" <mmcgintyNG(a)sSpUaCmKSdeprecatethis.com> wrote in message
news:OMmv8lotKHA.4860(a)TK2MSFTNGP05.phx.gbl...
> Greets,
>
> A company I work for has granted ODBC access to some of their clients (a
> risky proposition, I totally agree.) I have been tasked with analyzing
> the load these clients incur, and in the course of that, I noticed some
> calls to sp_table_statistics2_rowset were taking upwards of 100 seconds to
> return! That seems excessive so I'm considering restricting permissions --
> does anyone know what [if anything] that will break?
>
> I also noticed the application was "Microsoft SQL Server", does that mean
> these calls have something to do with linked servers?
>
> I did search MSDN, 'bing' returned 2 links to the same article, which
> listed this SP as being documented in BOL... sadly, it is not. (When is
> MS going to just concede the search thing and contract Google to do it
> right?) :-)
>
>
> TIA,
> -Mark
>

From: Mark McGinty on 26 Feb 2010 02:53

"Michael Coles" <admin(a)geocodenet.com> wrote in message
news:632EAD0C-C253-43A2-893C-A8B9B6EE5AB4(a)microsoft.com...
> That's a system stored proc that's used by client connectivity APIs. If
> you disabled permissions or something like that you'd probably have a hard
> time connecting to your server using some client APIs (presumably ODBC in
> this instance).

Umm, it doesn't get called often enough for that to be likely, out of almost
200K rows of trace data, it only gets called 5 times.

I thiink I'll look for it in next weeks traces, to make sure it's really an
issue; if so I'll abuse a VM to get to the bottom of it.


Thanks,
Mark



> Thanks
>
> Michael Coles
> SQL Server MVP
> Author, "Expert SQL Server 2008 Encryption"
> (http://www.apress.com/book/view/1430224649)
> ----------------
>
> "Mark McGinty" <mmcgintyNG(a)sSpUaCmKSdeprecatethis.com> wrote in message
> news:OMmv8lotKHA.4860(a)TK2MSFTNGP05.phx.gbl...
>> Greets,
>>
>> A company I work for has granted ODBC access to some of their clients (a
>> risky proposition, I totally agree.) I have been tasked with analyzing
>> the load these clients incur, and in the course of that, I noticed some
>> calls to sp_table_statistics2_rowset were taking upwards of 100 seconds
>> to return! That seems excessive so I'm considering restricting
>> permissions -- does anyone know what [if anything] that will break?
>>
>> I also noticed the application was "Microsoft SQL Server", does that mean
>> these calls have something to do with linked servers?
>>
>> I did search MSDN, 'bing' returned 2 links to the same article, which
>> listed this SP as being documented in BOL... sadly, it is not. (When is
>> MS going to just concede the search thing and contract Google to do it
>> right?) :-)
>>
>>
>> TIA,
>> -Mark
>>
>


From: Michael Coles on 26 Feb 2010 04:52
"Mark McGinty" <mmcgintyNG(a)sSpUaCmKSdeprecatethis.com> wrote in message
news:elwuXjrtKHA.4636(a)TK2MSFTNGP06.phx.gbl...
>
> "Michael Coles" <admin(a)geocodenet.com> wrote in message
> news:632EAD0C-C253-43A2-893C-A8B9B6EE5AB4(a)microsoft.com...
>> That's a system stored proc that's used by client connectivity APIs. If
>> you disabled permissions or something like that you'd probably have a
>> hard time connecting to your server using some client APIs (presumably
>> ODBC in this instance).
>
> Umm, it doesn't get called often enough for that to be likely, out of
> almost 200K rows of trace data, it only gets called 5 times.
>
> I thiink I'll look for it in next weeks traces, to make sure it's really
> an issue; if so I'll abuse a VM to get to the bottom of it.


Umm, I'm not following your logic here. Is the line of reasoning "that
system stored procedure is not called very often so it's not important"? If
so, you might want to rethink that strategy. There are system stored
procedures that are called very rarely but are extremely important to the
proper functioning of your server.

Look, before you do something you're going to regret 30 seconds later you
might want to do just a tiny bit of research. Try cruising over to this
page in Books Online before you put your server in an unsupported
configuration: http://msdn.microsoft.com/en-us/library/ms187961.aspx

As I told you this procedure is on the list of system stored procedures.
It's under the heading "The following stored procedures are not
documented:".
That's within the section titled "API System Stored Procedures".
The first two sentences of that section read:

"Users that run SQL Server Profiler against ADO, OLE DB, and ODBC
applications may notice these applications using system stored procedures
that are not covered in the Transact-SQL Reference. These stored procedures
are used by the Microsoft SQL Server Native Client OLE DB Provider and the
SQL Server Native Client ODBC driver to implement the functionality of a
database API."

Then shoot on over to this little article. The article is specifically
about security, but it mentions what you're considering in the much broader
sense of all system stored procedures:
http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx.

Notice the line that reads:

"Removing the system stored procedures results in an unsupported
configuration."

So what does this mean for you? Basically it means if you try to disable or
remove the system stored procedures listed on the first page you visited
you're going to run into two issues:

(1) You'll probably run into problems connecting to your server with some
connectivity drivers (the driver you're using obviously needs it; whether
it's once ever 40,000 rows or once every 1 row is pretty much irrelevant),
and

(2) You'll put your server into an unsupported configuration. This has big
implications for the type of support you can expect [think hotfixes, service
packs, CSS calls, etc.]

Since this procedure is on the list of "undocumented" procedures you don't
know how many drivers depend on it, to what extent, or even what it does.
Those "unknown-unknowns" tend to be the worst little things to mess around
with blindly.

I'd recommend against it. Microsoft recommends against it. But in the end
it's your server. Best of luck!

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

 |  Next  |  Last
Pages: 1 2
Prev: Need help with the Query.
Next: Reference integrity between two databases