| Author |
Topic |
|
vitoco
Starting Member
22 Posts |
 Posted - 2007-12-06 : 09:58:16
|
| Experts, I've run a profiler trace on a production 2k5 server for two days, resulting in more than 4GB of 50MB rollover files. Profiler was run with default settings plus some columns and a filter for just one database by name.I want to run Tuning Advisor over this huge log, but it seems that this tool can take one file at a time, but I need only one report instead of more than 80 with different suggestions.I'm trying to discard event records that aren't "RPC:Completed", "SQL:BatchCompleted" and "SP:StmtCompleted", in order to generate only one trc file that sumarizes all my capture.Is there a way to do that? Any tool available?I was thinking about to use the "fn_trace_gettable" method, but I don't want to run the Advisor over a database. BTW, is it possible to filter during the "select into" or "insert" with a "where"? If so, what should I filter?I can write a file manipulation tool for this task if I know the trc file format, but I didn't find that info. Any hint?Thanks a lot...++Vitoco |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-06 : 10:39:04
|
You can import all trace files to a table in your database and then export all SQL commands to a textfile and have index tuning advisor check the text file.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
vitoco
Starting Member
22 Posts |
Posted - 2007-12-06 : 11:05:42
|
quote:
Originally posted by Peso
You can import all trace files to a table in your database and then export all SQL commands to a textfile and have index tuning advisor check the text file.
I guess that doing an export of SQL commands will lose some useful info like statement's duration. BTW, I'm not sure if advisor uses that traced fields.... and I don't have enougth DB space available to load all that data  ++V |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-12-06 : 12:16:52
|
| Usually 200-500K rows recorded during day peak hours are enough to make the analysis.Why are you running SQL profiler for 2 days? |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-12-06 : 12:18:22
|
| It should keep all information in the trace file.=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-12-06 : 12:20:50
|
quote:
Originally posted by vitoco
Experts, I've run a profiler trace on a production 2k5 server for two days, resulting in more than 4GB of 50MB rollover files. Profiler was run with default settings plus some columns and a filter for just one database by name.I want to run Tuning Advisor over this huge log, but it seems that this tool can take one file at a time, but I need only one report instead of more than 80 with different suggestions.I'm trying to discard event records that aren't "RPC:Completed", "SQL:BatchCompleted" and "SP:StmtCompleted", in order to generate only one trc file that sumarizes all my capture.Is there a way to do that? Any tool available?I was thinking about to use the "fn_trace_gettable" method, but I don't want to run the Advisor over a database. BTW, is it possible to filter during the "select into" or "insert" with a "where"? If so, what should I filter?I can write a file manipulation tool for this task if I know the trc file format, but I didn't find that info. Any hint?Thanks a lot...++Vitoco
If you import into a table you can filter on the event type and then export back to a trace file. You can read the trace file directly using the SMO API. (http://www.sqlteam.com/article/reading-a-trace-file-using-charp-in-sql-server-2005)Can you import some number of trace files into SQL Server, deleting the event types you don't want and then export back out to a trace file?=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
vitoco
Starting Member
22 Posts |
Posted - 2007-12-06 : 13:56:31
|
quote:
Originally posted by evilDBA
Why are you running SQL profiler for 2 days?
We have a 500+ tables DB which holds 2 main systems that share many tables. Those systems have different behavior during the day, with some usage peaks on some transactions that may conflict and we are not aware of. So, we want to get the big picture.quote:
Originally posted by graz
Can you import some number of trace files into SQL Server, deleting the event types you don't want and then export back out to a trace file?
How can I export back to a trace file?Thanks again...++V |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-12-06 : 15:16:58
|
| Open up the trace table in Profiler and save it back to a trace file.=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
vitoco
Starting Member
22 Posts |
Posted - 2007-12-10 : 16:36:01
|
I'm getting an error while loading the first 50MB trace file: running out of space on tempdb. As the filegroup is set as autogrowth, it took every available byte on disk C... tempdb.mdf growed to more than 3.8GB, and I'm sure that at least 2.5GB where took by "fn_trace_gettable"  I tried both SELECT INTO and INSERT SELECT statements without success... Both took the space that the admin recovered by deleting some files from the filesystem.How much temporary space do fn_trace_gettable require to read one 50MB trace file? Am I doing something wrong?Thanks again...++Vitoco |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-12-10 : 17:08:58
|
| set the second parameter of the fn_trace_gettable to 1if it's set to default it will try to read all roll over files in one transaction and it will fill your tempdb like a rocket!_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
vitoco
Starting Member
22 Posts |
Posted - 2007-12-10 : 18:07:53
|
| Gotcha! I had set it to zero, not "default". So, those are synonyms.It seems that the resulting table will grow 50% more than the trace file. So my table will use 7.5GB... It also seems to be the same that my mdf, and the ldf will stay in 250MB.It was not posible to get the temp space used by file. For the first file, as it took 30 seconds to load 50MB, I could say that it used about 500MB of tempdb database, because it took about 2 mintues to raise an exception when the tempdb.mdf was 2.2GB and almost 4 minutes when it growed up to 3.8GB. BUT next files took only 10-15 seconds to be proccessed...Now, I have to find a simple procedure to restore the tempdb to it's original size... I'm not the DB owner!!!++Vitoco |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-10 : 22:00:40
|
| Leave it if the server doesn't have disk space issue. |
|
|
|
vitoco
Starting Member
22 Posts |
Posted - 2008-02-12 : 16:42:07
|
quote:
Originally posted by graz
Can you import some number of trace files into SQL Server, deleting the event types you don't want and then export back out to a trace file?
Is there a way to export a table back to a trace file without using the Profiler's GUI?I want to manage this in a batch process.Thanks! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-02-12 : 16:57:40
|
| no. but you can use bcp to export data to a csv and then import it back in._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2008-02-12 : 17:04:46
|
quote:
Originally posted by vitoco
quote:
Originally posted by graz
Can you import some number of trace files into SQL Server, deleting the event types you don't want and then export back out to a trace file?
Is there a way to export a table back to a trace file without using the Profiler's GUI?I want to manage this in a batch process.Thanks!
I think "not easily" might be a better answer. You can use the Trace SMO objects to write to a trace file. You'd have to custom write a console application but you could do it.=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
JeffK95z
Starting Member
19 Posts |
Posted - 2008-02-22 : 13:52:21
|
| I know it may be to late, but if you are able to re-run the trace files you can set filters within the actual trace itself.That way, the unwanted data will simply not be logged at all.look into sp_trace_setfilterAlso, please please please take tempdb (and all databases for that matter) off the C Drive :) |
|
|
|
|
Many big trace files