Wednesday, March 28, 2012

how to create performance statistics (was "A Challenging Question")

How does one figure out, I mean compare the results after some changes have been done to the SQL Server, I mean the method in which the comparision is done, lets say I have a query I ran it it gave me the results in 40 seconds , I ran it again this time it took 30 seconds, then I again ran it it took 35 seconds, I then created an index , this time the query ran in 30 seconds ... how does one compare such things , I mean i need to give stats as too what kind of performace has takern place ... please helpo , I need to knw as a DBa how would you convince your maanger that becasue of some changes the performance has improved, cause when you ask the users they say its ok , we dont see the differnece & stuff , please help.Add this code before ur query.

set statistics io on
go
set statistics time on
go


run ur query before making any changes,note down Execution Time, IO (specially logical read).
Make changes to ur query or add index,
remove data from cache and force sp to recomplie if u are using sp, by running below code

DBCC DROPCLEANBUFFERS
go
DBCC FREEPROCCACHE
go


add the first code I mentioned,run the sql statement again , note down execution time,IO.

compare the result.show to ur manager if it is improved,otherwise take holiday!

No comments:

Post a Comment