Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jogin malathi
Posting Yak Master
117 Posts |
 Posted - 2007-06-27 : 05:25:00
|
| Hi allI want to check the query performance How I should starTI mean what is the plan to be followedThanks In AdvanceMalathi Rao |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-27 : 05:27:04
|
| the first thing you must do is check your execution plan.then check index fragmentation DBCC showcontig will help you with that.if fragmentation is great rebuild indexesif not update statistics.that's about it for a start._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-27 : 05:37:56
|
| Check the execution plan.Look for big thick lines and check for table scans and large rowcounts.That will (probably) show you where the bottlenecks are.You can then index or recode the query to compensate.Note that it's not exact - it doesn't take into account what is already in memory so may lead you astray but it's a good start.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-27 : 06:01:00
|
I do it somewhat differently, but I suppose its the same really ...I don't feel the Thick Line thingie helps me much. However, I'm an old hand, so it maybe that I have a "nose" for what the issues are, and someone new to it can't really expect to have that!I look at the Text Plan, and review which indexes are being used, and what table scans there are.I also look at the Logical I/O and number of Scans. I try various things to reduce those. Then I'm done. I'm aiming not to spend too long on it ... we produce our own Statistics of which Sprocs are running slow (relative to smoothed average), and also which SProcs have got slower over time.Before you make any performance tests make sure you have reindexed / updated stats so that the Query Plan that is produced is optimal for the data available (you also need to make sure you have a representative set of data - both Size and Distribution).You may also want to clear the server's cache completely.-- Clear cache (for level playing field)-- Take **GREAT** care if you plan to run these on a Live server!!DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE-- Comment in the SHOWPLAN to see the Query Plan, OR the STATISTICS to see the logical stats-- SET STATISTICS PROFILE ON; SET SHOWPLAN_TEXT ON -- If Temp Tables involved-- SET SHOWPLAN_TEXT ONGO-- SET STATISTICS IO ON; SET STATISTICS TIME ON-- ... put query here - e.g.:SELECT * FROM Northwind.dbo.ProductsSET STATISTICS IO OFF; SET STATISTICS TIME OFFGOSET SHOWPLAN_TEXT OFFGO Kristen |
|
|
|
|
|
|
query performance