SQL Server Performance Forum – Threads Archive

Monitoring table usage

Hi, We have a 8GB database that makes use of 2 filegroups on RAID 1+0 arrays. Performance Monitor shows that the disk that is used for the primary filegroup is used far more than the disk that has the secondary filegroup and the everage queue length on the first disk is too high as well. Therefore we think that reassing tables/indexes to filegroups could improve performance. I have app. 15 – 20 large tables that are frequently used. My question is "is there a way of monitoring the usage of the tables/indexes for some time (say a week) to get an idea of the tables/indexes that should be moved from the primary to the secondary filegroup. (like ‘set statistics IO on’ would do for one query or batch) Thanks

PROFILER is the tool to monitor the usage of indexes and other queries against the database, by submitting the trace to index tuning wizard will give you more information on index recommendations too. Ensure to maintain and schedule the db maintenance & dbcc checks on the database. http://www.winnetmag.com/SQLServer/Article/ArticleID/38789/SQLServer_38789.html for tracking index usage.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

After read link, I run Profiler with ObjectID,DatabaseId and IndexID.
Profiler show ObjectId, DatabaseId but IndexId is allways blank.
I’m missing something?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Check what indexes were associated to this table. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

IndexId only work with Objects Event and not with others events in trace.
Even when I check all under Objects Event and use appropiate columns (indexId, objectId, objectype,etc), never show somethink in IndexId trace. The only way I found to know what indexs are used is via hard work I’ve post in Tips & scripts.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Appreciate the feedback. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I ‘ve the same problem as Luis: relevant trace columns are always blank when trying to see what indexes are used.

Fyi, you can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index. http://www.winnetmag.com/SQLServer/Article/ArticleID/38789/SQLServer_38789.html article about tracking index usage. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>