Check index usage

Question

Post reply

Check index usage

newbieuser

SSCrazy Eights

Points: 9423
More actions
September 10, 2008 at 7:32 am

#123322

Hi all,
I am pretty new to the SQl server, I am trying to monitor a session connecting to the database from our application. I need to find out if it is using the correct index because the client reported slow performance on one of the screen on the application side.
I'm not sure if I can see details of a user session like the index usage, query etc from the SQl Server Management Studio. I can see the activity monitor to track the session but if anybody knows how I can see the details of the session Please let me know. I will appreciate that. Thanks a lot.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ianT SSCrazy Points: 2956 More actions · Answer 1

Hi

You could use SQL profiler - check the performance objects. Performance - Showplan XML gives a graphic of the query.

Don't run a profile for too long - it may hit the server performance.

Also, sys.dm_db_index_usage_stats gives some info on how often indexes etc are accesed.

Slow performance may be caused by other issues - high server activity etc.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 2

Thanks, Do you know of a site I can download the trial version of the profiler??

I tried a few, the link has expired.. Thanks again

Clive Strong SSChampion Points: 11222 More actions · Answer 3

You can also view the execution plan from SQL Server Management Studio.

Paste the stored procedure or query into the query window. Right click in the query window and choose either "Display Estimated Execution Plan" or "Include Actual Execution Plan"

Display will show you the plan immediately, include will require you to run the code.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 4

But this is not executing query from the front end.. it is through the application so I have to go with the session details..

thanks

ianT SSCrazy Points: 2956 More actions · Answer 5

The profiler should be on your server -

Start -> Programs -> Microsoft SQL Server 2005 -> Performance Tools -> SQL Server Profiler

Create new trace (File - New Trace ) and select a blank template. On the events tab, make sure 'show all events' is ticked. Select a Performance object. Select column filters to exclude the stuff you don't want.