Category Archives: Tools

Now SQL Server 2008 has BPA too

Posted on by

This has been an ask from the community for a long time now. We just launched SQL Server 2008 R2 Best Practices Analyzer. The SQL Server rules are executed using the Microsoft Baseline Configuration Analyzer framework. The rules are all Powershell based. So you need Powershell 2.0 and MBCA 2.0 to use SQL Server 2008 R2 BPA. This version of BPA has an extensive set of rules which can validate a lot of commonly known issues in areas like SQL Setup, Engine, Replication etc. This makes the work of a DBA much easier. Another cool feature about this tool is that you can run remote scans also against other SQL instances.

Addendum: April 13th, 2011

Note: It works with SQL Server 2008 and SQL Server 2008 R2.

Upcoming Zeollar Presentation

Posted on by

I will be presenting a 30 minute demo on how to perform post mortem analysis for SQL Performance issues using RML Utilities and SQL Nexus on 24th June, 2010. The recorded and live session will be available on Zeollar. Zeollar is a Microsoft India DPE initiative that gets you the latest technical content on a daily basis in different channels. Think of it like a personal television that switches on every day at a specified hour allowing you to switch channels and view the channel of your interest.

You can download the calendar invite from here.

How to create a Profiler Trace during Server startup

Posted on by

I think I answered this question more than once on multiple different forums. So, I thought it was time to create a blog post on it.   

One of my previous post has the steps to create a .sql file containing the server side trace definition. Once you have the .sql file generated by the SQL Profiler Trace GUI. Use the script to convert that into the stored procedure with the following changes:   

declare @tracefilename nvarchar(255) 

set @tracefilename = N'D:\StartupTrace'+CAST(DATEPART(d,GETDATE()) AS varchar(2))+CAST(DATEPART(M,GETDATE()) AS varchar(2))+CAST(DATEPART(YYYY,GETDATE()) AS varchar(4))+CAST(DATEPART(HH,GETDATE()) AS varchar(2))+CAST(DATEPART(MI,GETDATE()) AS varchar(2)) 

exec @rc = sp_trace_create @TraceID output, 0, @tracefilename, @maxfilesize, NULL

You will have to add the lines highlighted above and the make a change for the trace file name in the line that is not highlighted. This will ensure that every time your SQL instance starts up, you have a unique trace file name. If the filename is not unique, then the second time the service restarts you will get a trace creation error stating that the file already exists. You will see the following error in your SQL Errorlog:   

Launched startup procedure ‘usp_startup_trace’.

Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070050(The file exists.).

Error: 19062, Severity: 16, State: 1.Could not create a trace file. 

After this is done, create the stored procedure to create the trace definition. Let’s say the proc name was “usp_startup_trace”.   

Now, you will need to mark this stored procedure as a startup procedure using the following:

EXEC sp_procoption 'usp_startup_trace','startup',true

 Once that is done, you will need to set the configuration options for the instance to scan for startup procedures. The default is set to 0.   

EXEC sp_configure 'scan for startup procs',1 RECONFIGURE

 When your SQL instance restarts the next time a new file will be created for the profiler trace and sys.traces will show you new trace active on the server.  

Reference: sp_procoption

How to open Management Studio with the Object Explorer and a Query Window from command line

Posted on by

SQL Server Management Studio provides you the option of launching from command line. If you want to launch SSMS with a New Query Window and Object Explorer using command line, then you need to do the following changes.

Tools –> Options will open up the Options page for SSMS.

Under that you would see an At Startup property under Environment –> General. Use the drop down list to select the “Open a Object Explorer and New Query”. The default is shown below in the screen shot.

image

Once you have made the change, you can use the following command to launch SSMS connected to the instance you want:

ssms -S <server name> -E –nosplash

SSMS Command Line Options

—————————
Microsoft SQL Server Management Studio
—————————
Usage:
ssms.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P password] [-E] [file_name[, file_name]] [/?]

    [-S    The name of the SQL Server instance to which to connect]
    [-d    The name of the SQL Server database to which to connect]
    [-E]    Use Windows Authentication to login to SQL Server
    [-U    The name of the SQL Server login with which to connect]
    [-P    The password associated with the login]
    [file_name[, file_name]] names of files to load
    [-nosplash] Supress splash screen
    [/?]    Displays this usage information

Clarifying misconception about RML Utilities

Posted on by

I remember in the past month addressing at least three different queries regarding RML Utilities on MSDN w.r.t. the co-relation between the Batches and Statements. Yes, it is true that a Batch will have one or more T-SQL statements. But if the statements do not have SP:StmtStarting and SP:StmtCompleted, then the Batch will not have an entry in the ReadTrace.tblStatements. This is documented in the RML Utilities help file. 

You can verify if the trace has any SP:StmtStarting or SP:StmtCompleted using the following query: 

  

 

select count(*) from fn_trace_gettable ('<trace file with full path>',null) where eventclass in (44,45)

From the help file: 

ReadTrace.tblStatements 

This table contains one row for each SP:StmtStarting or SP:StmtCompleted event in the trace. Similar to the ReadTrace.tblBatches table, the completed event is stored when it is available. Otherwise, the starting event is saved so that you can tell that a query was running when the trace was stopped.