Month: June 2011

How-to: Decode Reason Codes in SQL Azure


I read this article in MSDN, which clearly explains to How to Handle and Decode the Error Reason codes returned from SQL Azure

http://msdn.microsoft.com/en-us/library/4cff491e-9359-4454-bd7c-fb72c4c452ca#bkm_reason_codes

http://msdn.microsoft.com/en-us/library/gg491230.aspx

I hope you also find it useful !!!

 

 

 

 

 

Advertisement

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

image

Blocking and Resource Wait Statistics Report

clip_image001

Bottleneck Analysis Report

clip_image002

Bottleneck Analysis –Drill down sub report

clip_image003

Bottleneck Analysis –Drill down sub report – Wait Statistics

clip_image004

clip_image005

SQL Server 2005 Performance Statistics Reports

clip_image006

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.

clip_image007

clip_image008

SQL Server 2008 Performance Statistics Reports

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

clip_image009

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

clip_image010

clip_image012

Query Hash Report – Performance Overview Drilldown report

clip_image013

clip_image014

Query Hash Report – Top Unique Batches Drilldown charts

clip_image015

Query Hash Report – Top Unique Batches Details report

clip_image017

Query Hash Report – Resource Consumption report

clip_image018

clip_image019

clip_image020

clip_image022

clip_image024

Query Hash Report – Top Unique Batches Details Report

clip_image028

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

clip_image029

Interesting Events – Drilldown Report

clip_image030

clip_image032

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

  1. BEGIN TRY
  2. BEGIN TRANSACTION
  3. /* Insert or Update or Delete statements */
  4. COMMIT TRANSACTION
  5. END TRY
  6. BEGIN CATCH
  7. INSERT INTO ErrorLog (ErrorNumber, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure, ErrorRelease)
  8. SELECT ERROR_NUMBER() AS ErrorNumber,
  9. ERROR_SEVERITY() AS ErrorSeverity,
  10. ERROR_STATE() as ErrorState,
  11. ERROR_LINE () as ErrorLine,
  12. ERROR_PROCEDURE() as ErrorProcedure,
  13. ERROR_MESSAGE() as ErrorMessage
  14. IF (XACT_STATE()) = -1
  15. BEGIN
  16. ROLLBACK TRAN
  17. END
  18. ELSE IF (XACT_STATE()) = 1
  19. BEGIN
  20. COMMIT TRAN
  21. END
  22. RAISEERROR(‘Application Error Occurred. Please contact administrator’, 16, -1)
  23. 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

clip_image001

 

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.

clip_image001[5]

Query to copy database diagrams

  1. INSERT INTO [DESTINATIONDB].[dbo].[sysdiagrams]
  2. ([name]
  3. ,[principal_id]
  4. ,[version]
  5. ,[definition])
  6. SELECT [name]
  7. ,[principal_id]
  8. ,[version]
  9. ,[definition]
  10. 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.

%d bloggers like this: