SQL Server – Page 8 – SQLXpertise

SQL Nexus–How to generate Reports from Performance Data collected and imported ?–Part 3

In previous posts we saw How to install, configure and collect performance data from SQL Server 2005/2008/2008 R2 ? and How to import Performance Data using SQL Nexus ?

In this post we will see How to generate Reports in SQL Nexus

The following are the list of reports available

Blocking and Resource Wait Statistics Report

Bottleneck Analysis Report

Bottleneck Analysis –Drill down sub report

Bottleneck Analysis –Drill down sub report – Wait Statistics

SQL Server 2005 Performance Statistics Reports

Bloaking and Wait Statistics and Bottleneck Analysis reports are linking to Reports we saw earlier

Spinlock Statistics Report – Spinlock is a lightweight, user mode synchronization object used to protect a specific structure. The goal of this report is to see there are no collissions and spin.

SQL Server 2008 Performance Statistics Reports

Blocking and Wait Statistics, Bottleneck Analysis and Spin Lock Stats reports links to the above reports we saw.

Query Hash Report – This report show you the list of queries with details of How many times it executed, How many query plans were created, Total CPU time taken, Total Duration to execute the query and Total Logical IO Reads

Query Hash Report – Performance Overview Drilldown report

Query Hash Report – Top Unique Batches Drilldown charts

Query Hash Report – Top Unique Batches Details report

Query Hash Report – Resource Consumption report

Query Hash Report – Top Unique Batches Details Report

Interesting Events Report shows the number of times events like Auto Stats Updation, Lock Escalation, Missing column statistics, etc… is occurring along with Trace Event ID, you can also drill down into each event and see the query caused the event as well

Interesting Events – Drilldown Report

I hope you all find this information useful… Please post your comments !!!

Error Handling in SQL Server 2008 R2–Questions Answered

With reference to the Question posted by Hima Bindu Vejella on http://himabinduvejella.blogspot.com/2011/06/how-did-you-handle-errors-in-sqlserver.html, I have posted these answers, I hope you all find this usefull

How did you handle errors in SqlServer 2008 R2?

We handle errors using

BEGIN TRY 
BEGIN TRANSACTION 
/* Insert or Update or Delete statements */ 
COMMIT TRANSACTION 
END TRY 
BEGIN CATCH 
INSERT INTO ErrorLog (ErrorNumber, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure, ErrorRelease) 
SELECT ERROR_NUMBER() AS ErrorNumber, 
ERROR_SEVERITY() AS ErrorSeverity, 
ERROR_STATE() as ErrorState, 
ERROR_LINE () as ErrorLine, 
ERROR_PROCEDURE() as ErrorProcedure, 
ERROR_MESSAGE() as ErrorMessage 
IF (XACT_STATE()) = -1 
BEGIN 
ROLLBACK TRAN 
END 
ELSE IF (XACT_STATE()) = 1 
BEGIN 
COMMIT TRAN 
END 
RAISEERROR(Application Error Occurred. Please contact administrator, 16, -1) 
END CATCH()

What are the different ways of handling errors in SQL Server?

1. We can handle errors by checking @@ERROR, if it’s not equal to 0, then we can use RAISERRROR to return error message to application

2. We can handle error by checking @@ERROR and @@ROWCOUNT in combination and then use RAISERROR or RETURN to return error message or code to application

3. We can use TRY..CATCH method

Which is the optimized way to handle errors?

The optimized way to handle errors is to create Error Log table with following columns (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, UserName, HostName, ErrorDateTime, ApplicationName) and then create a Stored Procedure to get these columns as Input Parameter except ErrorDateTime, for which we can use GetDate() and then call this Stored Procedure inside BEGIN CATCH..END CATCH block

What are the Built-In Objects available related to error handling?

The following are the built in objects related to Error Handling and Information

ERROR_NUMBER() – Returns Error Number of the Error that caused the CATCH block of TRY…CATCH to run

ERROR_SEVERITY() – Returns severity of Error that caused the CATCH block of TRY…CATCH to run

ERROR_STATE() – Returns State Number of the Error that caused the CATCH block of TRY…CATCH to run

ERROR_LINE () – Returns the line number at which an error occurred that caused the CATCH block of TRY…CATCH to run

ERROR_PROCEDURE() – Returns the name of the stored procedure or trigger where an error occurred that caused the CATCH block of a TRY…CATCH construct to be run.

ERROR_MESSAGE() – Returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run

@@ERROR – Returns the Error number of the last T-SQL Statement run

RAISERROR – Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

Sys.Messages – This is a Catalog view which contains the list of system defined and user defined messages

SP_AddMessage – To define a new user-defined error message in a SQL Server instance

sp_dropmessage – To drop a specified user-defined error message from a SQL Server instance

xp_logevent – Logs a user defined message in SQL Server log file and in the Windows Event Viewer. It can be used to send an alert in the background to administrators without showing a message to client

Print – To simply display the message, we can use PRINT statement followed with Error message

How can return Error information to Application ?

We can return error information back to application using RAISERROR Or RETURN

RAISERROR (‘Application Error Occurred’, 16, -1)

RETURN -1 –In Application we have to check for Return value and display corresponding message

SQL Server 2008 R2 Database Diagrams Questions Answered

What are the database diagrams in SQL Server?

SQL Server Database Diagram is visual model of a database. We can use only Tables in SQL Server database diagrams. Any changes made outside Database Diagram designer will reflect automatically in the diagram and vice versa.

How did you create them?

We can create them using built-in SQL Server Database Diagram designer tool in SQL Server Management Studio. There is no Undo and Redo option, so we need to be careful when deleting columns or removing relationships

Do you know where exactly the database diagrams are stored in SQL Server 2008 R2?

Database Diagrams are stored in dbo.sysdiagrams system table in the database we are creating diagram. This table will be created automatically, when we create the first database diagram in the database.

The structure of this table is as follows

Is there any way how you copy one database diagram to another database?

Yes, we can copy the diagram by copy the data of dbo.sysdiagrams table from source database to destination database, but the tables in the diagram should exist in the destination database or else while viewing or modifying the diagram, we will get the below error and corresponding tables will be removed from the diagram

“Table(s) were removed from the diagram because privileges were removed to these table(s) or the table(s) were dropped.

Query to copy database diagrams

INSERT INTO [DESTINATIONDB].[dbo].[sysdiagrams] 
([name] 
,[principal_id] 
,[version] 
,[definition]) 
SELECT [name] 
,[principal_id] 
,[version] 
,[definition] 
FROM [SOURCEDB].[dbo].[sysdiagrams]

For copying to remote servers, we can use Linked Servers and use Four part name or use SSIS to transfer data

How will you insert database diagrams into your design document?

We can use “Edit -> Copy Diagram to Clipboard” or “Database Diagram – Copy Diagram to Clipboard” menu option to copy the diagram and then paste it in the document required. If the diagram is bigger, we can paste it to Paint Brush or any image editor and then select specific portions and copy one by one in document.

We can also the print the database diagram as PDF, if we have PDF Print drivers such as BioPDF or CutePDF installed.

SQL Nexus–How to import Performance Data collected using PSSDiag ?–Part 2

In the last post, we saw how to install, configure and collect performance data from SQL Server 2005/2008/2008 R2, in this post, we will how to import that data using SQL Nexus and get ready to prepare Performance Analysis Reports

SQLNexus is a free tool developed by SQL Server support team and used for importing and analyzing performance data collected using SQL Diag and PSSDiag. Its helps identifying the Bottle necks, blocks, CPU consuming queries, Wait, Locks information, etc…

SQLNexus is freely available for download from http://sqlnexus.codeplex.com/

To run SQLNexus, you need to install the following prerequisites

.Net Framework 3.5

SQL Server 2008 SP1 Report viewer control (http://www.microsoft.com/downloads/details.aspx?FamilyID=bb196d5d-76c2-4a0e-9458-267d22b6aac6&DisplayLang=en)

SQL Server 2005 or above – To store imported performance data. You can also use SQL Server Express Edition

ReadTrace (RML Utilities for SQL Server)

x64 – http://www.microsoft.com/downloads/en/details.aspx?FamilyId=B60CDFA3-732E-4347-9C06-2D1F1F84C342&displaylang=en

x86 – http://www.microsoft.com/downloads/en/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en

After installing all prerequisites, download the SQLNexus from the above site and store it any local folder and then extract the zip file SQLNexus3.0.0.0.zip

Double click or Run “SQLNexus.exe”

On loading of the application, it will prompt you to connect to SQL Server instance on which you want to create the SQLNexus database, specify the Server Name, Authentication information and click “Connect”

Its not recommended to run this on your production server, since it will create lot of performance loads while importing the data and running the reports

If RML Utilities is not installed, you will get the below error message

Without RML Utilities, Trace files (.trc) will not be imported

On successful connection, you will see the above Main Dashboard screen, To start the import process, click on “Import” link left hand bottom side of the screen

Click Folder Browse icon and select the folder on which your Output data from PSS Diag and SQL Diag tool is stored.

Please make sure there is no spaces in the folder names, If you have space in the folder name, you will not be able to import the Trace files

To customize the import options, click on “Options”

Select the required options and if you want to retain the options you selected, click on “Save My Options”, for any reason, if you need to reset all the options you selected to default options, click on “Restore Default Options”

Use “Drop Current DB Before Importing” option to drop and recreate the database every time, this option will be helpful, if you want to load clean set of data every time.

Once finished customizing the options, click on “Import” button to proceed with the import

You will receive the above warning message, if you have selected “Drop Current DB Before Importing” option. click “Yes” to proceed with drop database and import

The above screenshots shows the import process and list of various files imported along with the number of records imported and also shows us whether the file imported successfully or not.

If you notice, the second line, Import for DBCC_MEMORYSTATUS_Startup.out file has failed, this is a known issue, there is no report currently available for DBCC MEMORYSTATUS, due to which this import will fail always, to follow up about this issue, please visit this discussion link CodePlex discussion forums http://sqlnexus.codeplex.com/workitem/11455

Now the import has completed successfully.

Click on File Menu and then click on “Run All Reports” to generate the reports

After the reports are generated, the Report link icons will change as follows

Since we don’t have any data related to SQL Server 2000, it will remain unchanged.

In the next post, we will explore the reports generated by SQLNexus and see various information available through them.

I hope you all find this information useful. Please post your comments !!!

PSSDiag / SQL Diag Manager–How to install, configure and collect performance data from SQL Server 2005/2008/2008 R2 ?–Part 1

PssDiag / SQL Diag Manager is a freely available graphical tool in CodePlex (http://diagmanager.codeplex.com/). It provides easy way to configure what data needs to be collected from SQL Server and Windows Server for troubleshooting SQL Server performance issues.

This tool is used by Microsoft SQL Server support team to collect data to troubleshoot performance issues.

Let us see how to install, configure and collect data

Step 1: Once downloaded the setup from the above specified link, run the Setup.exe and click “Next” on Welcome screen

Step 2: Select your installation folder and then click “Next”

Step 3: Click “Next” to confirm the installation

Step 4: Click “Close” to confirm the installation

Step 5: Once the installation is successfully completed, click on Start Menu and open “PSS Diag” and then click on “PSSDiag Configuration Manager”

Step 6: To configure the PSSDiag, Please specify the following information Machine Name (SQL Server Name), Instance Name (If Default Instance, specify *), Select authentication

Trace Template

By default, SQL Server 2008 is selected, PerfMon counters associated will be selected and for Database Engine, there are 3 trace templates available

Select the Trace template and specify the Maximum size, If you set it to “None”, file size will be unlimited, Please make sure you collect only Trace information for required events, selecting all events will degrade the performance of your SQL Server

Custom Diagnostics

Select the custom diagnostics you wish to collect, “SQL Memory Error” and “SQL Blocking” is not selected by default, these 2 are useful Custom Diagnostics to troubleshoot any blocking issues or long running query issues

Even though You see option to Select “SQL Server 7.0” and “SQL Server 2000”, this utility doesn’t support those versions of SQL Server.

Step 7: To configure Analysis service engine collection information, click “Analysis Services” tab, you can customize the PerfMon counters, Trace information and Custom Diagnostics

Step 8: Save the configuration – Once finished configuring, click on “Save” button on top

Specify the path on which you want to save the configuration output file and click “Ok”

Specify whether you are planning to run this on SQL Server 2008 or SQL Server 2008 R2

Once the configuration files are generated and packed as CAB file, the following message will be displayed. Click “Ok” to complete configuration and Exit the tool

Step 9: Run the PSSDiag and Collect the performance data

Open Window Explorer and go to the folder where the PSSD.CAB file is stored and then open it and extract the files. To extract the data, you can use any Zip tool such as 7 Zip or Win Zip or WinRAR

Once extracted, double click on “PSSDiag.cmd” or run it from Command Line

Click “Yes” to proceed with running the utility to start collecting performance data

SQLDiag utility will start with specified configuration and will display series of messages as displayed below

You can let run the tool as long as you want to collect the data. To stop collecting the data and exit the Tool, Press “Ctrl + C”.

The output from SQL Diag will be stored in folder named “Output”, from the folder where you are running the tool, Ex. If you are running the tool from “C:\Workspace\PSSConfig\”, then the output folder will be “C:\Workspace\PSSConfig\output”

The output folder will contain lot of different files containing the information collected.

In the next post, we will see how to import this collected information, run reports and find out how to identify the performance issues.

Share this:

Like this:

sp_dropmessage – To drop a specified user-defined error message from a SQL Server instance

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: