October 25, 2006 at 8:23 am
Is there a simple way to take a snapshot of the statistics on a given database. I've been asked to produce a weekly report detailing this information.
Thanks
-WM
October 25, 2006 at 11:54 am
Well, statistics of a database is a very wide term. Can you be more specific ?
* Noel
October 25, 2006 at 12:30 pm
Actually the word "Statistics" in the SQL Server universe has a very specific meaning, (Statistics are used by the query optimizer to choose the mose efficient query plans) but I'm pretty sure that you don't need a report on that kind of statistics.
Are you wanting a report on things like database size and/or usage?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
October 25, 2006 at 12:42 pm
its basically came up this way..
Someone ran a query on the production environemnt it took 36 seconds then ran the same query on the implementation box taking about 48 milliseconds. After running a job that updates the stats on the producution box. The response time returned to 48 milliseconds. I was actually looking for a way to determine how long its taking the stats to get outdated. The auto update option is set to true so I would think that this should be all set.
This is the query that was run
set @p2=2
declare @p4 int
set @p4=NULL
declare @p5 int
set @p5=NULL
@QueryString=N'smith',
@PageNumber=@p2 output,
@PageSize=15,
@TotalPages=@p4 output,
@TotalRecordCount=@p5 output,
@UserGUID='FE87CE8D-2355-4401-9742-EED8102117C7',
@SortExpression=N'LastName ASC'
October 25, 2006 at 2:04 pm
You might be seeing the effects of the statistics being updated. When a query is being compiled, SQL Server looks at the statistics that can be used to determine the appropriate execution plan. If the stats are determined to be out of date, they are updated before the query is compiled and run.
You might want to look at using the asynchronous statistics update option. When this is enabled, when SQL determines that stats are out of date, the query still compiles and runs using the out of date stats, and the stats are scheduled to be updated.
In a fairly large table that is inserted to or updated frequently, this might improve overall response times.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
October 25, 2006 at 3:32 pm
Yep that's what I did
Thanks
-WM
October 25, 2006 at 4:26 pm
Thank you for suggesting "the asynchronous statistics update option", per your suggestion I just tried it and it works just fine. Appreciate your help. David
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
October 25, 2006 at 8:50 pm
Glad I could help.
BTW if you're running 2000, about the only thing you can do in this situation is to turn off auto update stats and schedule statististics creation on a periodic basis.
This is generally not the greatest solution, but I've seen one situation where this is exactly what was needed.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply