February 7, 2009 at 4:28 am
I need to tune indexes on my sql2005 server but when I run a profiler trace using tuning template to get information about indexes, CPU becomes 50% to 70% busy
IS there any other way to get information about indexes? Can querying DMVs be trustworthy to apply the changes?
February 7, 2009 at 7:58 am
You could certainly take a gander at index_physical_stats and index_usage_stats, for starters. It kind of depends on what kind of tuning you are trying to do. Are you trying to defrag your indexes? See where you need indexes but don't have them? See where you have indexes but don't need them?
"Got no time for the jibba jabba!"
-B.A. Baracus
February 7, 2009 at 8:50 am
Have you tried running the trace as a server side trace rather than through profiler? That should have noticeably less impact.
1) Start the trace from SQL Profiler and stop immediately.
2) Export the trace to a script; File/Export/Script Trace Definition.
3) Open that script in SSMS and change InsertFileNameHere to a valid path.
February 7, 2009 at 10:07 am
peace2007 (2/7/2009)
I need to tune indexes on my sql2005 server but when I run a profiler trace using tuning template to get information about indexes, CPU becomes 50% to 70% busyIS there any other way to get information about indexes? Can querying DMVs be trustworthy to apply the changes?
DMVs are special addition to sql server 2005 and are helpful...u can google many queries and read BOL to check index info using DMVs..
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 7, 2009 at 11:10 pm
Thanks all for the comments 🙂
Have you tried running the trace as a server side trace rather than through profiler? That should have noticeably less impact.
1) Start the trace from SQL Profiler and stop immediately.
2) Export the trace to a script; File/Export/Script Trace Definition.
3) Open that script in SSMS and change InsertFileNameHere to a valid path.
I did the same but it returns traceID and stops. How can I use this to tune database?
February 8, 2009 at 2:51 am
peace2007 (2/7/2009)
Thanks all for the comments 🙂Have you tried running the trace as a server side trace rather than through profiler? That should have noticeably less impact.
1) Start the trace from SQL Profiler and stop immediately.
2) Export the trace to a script; File/Export/Script Trace Definition.
3) Open that script in SSMS and change InsertFileNameHere to a valid path.
I did the same but it returns traceID and stops. How can I use this to tune database?
the script should return the traceid and auto start the trace. Check your system and you'll find its running.
select * from ::fn_trace_getinfo(yourtraceid)
will show details of your active trace, check the value of property 5 it should be set to 1 which means its running
use
sp_trace_setstatus yourtraceid, 0 to stop the trace
sp_trace_setstatus yourtraceid, 2 to close the trace(remove the definition from the server)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 8, 2009 at 3:07 am
the script should return the traceid and auto start the trace. Check your system and you'll find its running.
select * from ::fn_trace_getinfo(default)
will show details of all traces active
My traceid is 2 and fn_trace_getinfo shows nothing for that;)
February 8, 2009 at 7:14 am
I did the same but it returns traceID and stops.
How did you determine it wasn't running? Querying sys.traces will show you the status.
[font="Courier New"]SELECT * FROM sys.traces[/font]
How can I use this to tune database?
SQL Profiler and a server-side trace generate the same output, a .trc file. You would use that the same way you were planning on with SQL Profiler.
Before applying the Tuning Advisor recommendations make sure you carefully review them and determine the consequences.
February 8, 2009 at 4:03 pm
select * from ::fn_trace_getinfo(default)
peace2007 (2/8/2009)
My traceid is 2 and fn_trace_getinfo shows nothing for that;)
using the exact command above what traces does it show?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 22, 2009 at 6:06 am
it works fine now
thanks a lot
February 22, 2009 at 6:24 am
I've created a Tuning trace and its run now
I'd like to know how I can analyze my trace. Shall I open the created trace file in DTS? Actually, can't find the file in the folder where I've specified in InsertFileNameHere but select * from ::fn_trace_getinfo(2) tells me that its run! Is there any other way to analyze it?
February 22, 2009 at 9:27 am
After the trace has been running for awhile stop it by setting the trace status as Perry had shown in a previous post.
You are probably thinking of DTA, Database Engine Tuning Advisor, not DTS. And yes, you can specify the trace file as the workload and change any tuning options relevant to your situation. Querying sys.traces is another way to see the status and find where of the .TRC file is.
Again, I personally wouldn't recommend applying the changes without reviewing them and understanding the impact. For example, I had a vendor send a script that was generated by DTA that they claimed would improve the performance of their application. After measuring the impact of that change there was no noticeable improvement because they had focused on a single query that was run relatively few times in relation to the overall work being done.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply