Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SELECT StatMan Expand / Collapse
Author
Message
Posted Friday, July 26, 2013 12:12 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:28 AM
Points: 155, Visits: 456
Does anyone know what the query below means? I'm running into this type of query pretty frequently an I'm not sure how to find out the problem, if there's any.


SELECT StatMan([SC0])
FROM
(
SELECT TOP 100 PERCENT [AT_field_3] AS [SC0]
FROM [dbo].[tablename]
WITH
(
READUNCOMMITTED
)
ORDER BY [SC0]
) AS _MS_UPDSTATS_TBL

Thanks.
Post #1478141
Posted Friday, July 26, 2013 12:30 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
It's a statistics update, probably auto-stats.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1478146
Posted Friday, July 26, 2013 12:41 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:28 AM
Points: 155, Visits: 456
I have a job running statistics, that's generating it?

use [DatabaseName]
GO
UPDATE STATISTICS [dbo].[tablename]
WITH FULLSCAN
GO

Do you know why is selecting some tables and not others?
Post #1478154
Posted Friday, July 26, 2013 12:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,231, Visits: 14,940
The cause for it to not select some tables is likely due to how your code is written.

Please provide the entire code that is generating your update stats so we can help you identify what is happening.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1478158
Posted Friday, July 26, 2013 12:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
HildaJ (7/26/2013)
I have a job running statistics, that's generating it?


Either that or the automatic stats updates, or both.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1478162
Posted Friday, July 26, 2013 1:15 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:28 AM
Points: 155, Visits: 456
This is all that the sql monitor tool throws out to the log:

SELECT StatMan([SC0])
FROM
(
SELECT TOP 100 PERCENT [AT_field_3] AS [SC0]
FROM [dbo].[tablename]
WITH
(
READUNCOMMITTED
)
ORDER BY [SC0]
) AS _MS_UPDSTATS_TBL

It seems to dump this log when I ran the maintance plan to update the stats. My maintenace plans has this:

use [DatabaseName]
GO
UPDATE STATISTICS [dbo].[tablename]
WITH FULLSCAN
GO
Post #1478173
Posted Friday, July 26, 2013 1:27 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
The Statman query is what's run internally for statistics updates.

What exactly is the problem here?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1478175
Posted Friday, July 26, 2013 1:32 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:28 AM
Points: 155, Visits: 456
I'm not sure if there's a problem, I have not seen this query before and I was inquiring information about it. I see now that updating statistics generates these queries. It seems that this is normal operation on part of sql server.
Post #1478178
Posted Friday, July 26, 2013 1:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,231, Visits: 14,940
HildaJ (7/26/2013)
I'm not sure if there's a problem, I have not seen this query before and I was inquiring information about it. I see now that updating statistics generates these queries. It seems that this is normal operation on part of sql server.


Yes it is




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1478180
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse