Automate Trace file import process with SSIS

Automate Trace file import process with SSIS

I have previously written two articles on this subject “Profiler Trace”.

Start and stop server side profiler trace automatically in SQL Server 2012: This article shows how to create profiler trace file automatically for our business hours.

Read SQL Server Profiler Trace files with SELECT statement: This article shows how to read trace file with TSQL which was generated by the previous article.

Reading trace file manually with TSQL and after reading the trace file, delete those trace file is, somehow, repetitive as well as boring work to do. I used to create one SSIS package which reads profiler trace file and insert data into SQL Server table, delete old trace file and then remove 7 days old trace data from SQL Server table.

Let us create one SSIS project in Visual Studio 2010 and follow the steps given:

1.) Have one “Execute SQL Task” in SSIS Package. Assign following INSERT statement in “SQLStatement” property of Execute SQL Task and create one connection of your database.

[sourcecode language=”sql”]

Insert into Profilerlog

SELECT

ServerName

,DatabaseName

,SPID AS SessionId

,ObjectName

,EventClass AS EventNumber

,RowCounts

,CASE WHEN Duration IS NOT NULL THEN Duration/1000000.00 ELSE Duration END AS DurationInSeconds

,CPU AS CPUInMiliseconds

,StartTime

,EndTime

,TextData

FROM fn_trace_gettable(‘D:\TraceCollection\Adventureworks2012.trc’, DEFAULT)

go

[/sourcecode]

You must have “ProfilerLog” table in your database which can take output of the SELECT query we have executed on trace file. I have my trace file at “D:\TraceCollection\Adventureworks2012.trc”, you can have your own path. Have a look at screen capture of the Execute SQL Task.

1ExecuteSQLTask

2.) Once we have imported data from trace file, we can delete all trace file which we have already read so let us have “Foreach Loop Editor” and iterate it for each trace file we have in our folder so that we can remove it. Set the property of Foreach loop as displayed in screen capture.

2foreach

3.) You have to create variable “FileName” for package and it should be set to 0 as an Index in Foreach Loop’s “Variable Mappings” tab. You can refer above screen shot where you can find “Variable Mappings” right below “Collections” in left hand side.

4.) put “File System Task” inside foreach loop container and set “Delete file” in Operation property as given in following screen shot.

3FileSystemTask

5.) now have one more execute SQL task under the foreach loop and give following query which will remove 7 days old data from ProfilerLog table.

[sourcecode language=”sql”]

delete from Profilerlog where convert(varchar(10),StartTime,101) < convert(varchar(10),getdate()-7,101)

go

[/sourcecode]

6.) finally your package will looks like this:

4PackageLook

Now you are ready to schedule this package in your SQL Server Agent job which runs once (may be at midnight) and import trace file.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

SQL Server profiler and Database Engine tuning adviser in SQL Server 2005

SQL Server 2005 came up with so many tools and ways to keep watch on the performance of your databases. Today I am going to introduce you one interesting tool of SQL Server 2005 which is SQL Server Profiler with which you can create a trace file or table to see what is going in your server at specific time, later on you can use that trace file/table in your Database engine tuning adviser to get help from SQL Server itself to know what area you need to improve.
Let us follow the simple steps given below and check database whether anything is going wrong.
1)  Open SQL Server Profiler from Start-> All Programs->Microsoft SQL Server 2005-> Performance Tools->SQL Server Profiler
2.) Click on File->New Trace
3.)It will open Profiler windows where you have to give your SQL Server credential
4.) Give the Trace Name
5.) Select the template, there are many types of templates available, you have to choose right one for you.
6.) Since we can save trace into file/table, we will choose check box of “Save to File” give the name and path of the file, I have save the file to C drive with TestTrace name. have a look at below screen shot.
7.) Go to “Events Selection” tab from where you can select/deselect events and column, I kept everything as it is.
Have a look at it in screen shot below.
8.) click on “Run” button and it will start capturing data, please be informed that, tracing will make your server’s performance bit slow so use this whenever you really wants to do some analysis, don’t keep it running 24*7

9.) When you captured your desired data, stop tracing with red square button seen under “Windows” menu.
10.) Now open your “Database Engine tuning adviser” to get suggestion about how you can improve your T-SQL performance, observe screen capture given below.
11.)  Give Name to your trace under “Session Name” and select “File” radio option as we have kept tracing in our file, don’t forget to give file name and path there.  Since trace is server level operation, select database for “Workload analysis” and select database for tune. 






12.) Once you select everything, click on “Start Analysis” button under “View” menu and you will see SQL Database Engine tuning adviser” to make some process, as soon as that process gets finish, you can see two new tab in right hand side with the name “Recommendations” and “reports”. Under “Recommendation” tab, you will see what table of your selected database needs what, Indes, state update etc. you will not only see recommendation but you will see “Defination” of those recommendation as well and estimated improvement after doing those changes. Have a look at below screen 
Happy Observing!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah