SQL Server Performance Dashboard

Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SSMS. These reports will allow the DBA’s to quickly identify whether there is a current bottleneck on their system and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.

Common performance problems that the dashboard reports may help to resolve include:

– CPU bottlenecks (and what queries are consuming the most CPU)
– IO bottlenecks (and what queries are performing the most IO)
– Index recommendations generated by the query optimizer (missing indexes)   
– Blocking
– Latch contention

The information captured in the reports is retrieved from the DMV’s. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring the DB server.

Here are the detailed steps of Installation/Configuration of Performance Dashboard-

(a) Installing SQL Server 2012 Performance Dashboard Reports

 The following steps will install the Performance Dashboard Reports-

 1) Open the below link in your web browser and download SQL Server 2012 Performance Dashboard Reports:  http://www.microsoft.com/en-us/download/details.aspx?id=29063&WT.mc_id=aff-n-in-loc–pd     
2) Navigate to the location where you saved the SQLServer2012_PerformanceDashboard.MSI file and double-click it. You will receive a welcome screen. Click ‘Next’.
3) The next screen will present you with the license agreement. Read the agreement, select ‘I accept the terms in the license agreement’, and click ‘Next’.
4) The next screen will ask you the required registration information. Enter the details and click ‘Next’.
5) You will be asked to choose the program features for installation. Go with the default selection and click ‘Next’.
6) On the next screen, click ‘Install’ to begin the installation process. You can choose to click ‘Back’ button to make any other changes to your installation settings.
7) Click ‘Finish’ to exit the installation wizard. Now, you have successfully installed SQL Server 2012 Performance Dashboard Reports.

(b) Configuring SQL Server 2012 Performance Dashboard Reports

 After finishing the installation, it is necessary to configure the SQL Server to use the Performance Dashboard toolset you have just installed. The default installation directory for SQL Server 2012 Performance Dashboard Reports is ‘C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\’. Navigate to this location, search for the ‘setup.sql’ file, double-click it to open the Performance Dashboard Reports in SQL Server Management Studio. Next, establish connection to the SQL Server Database Engine instance where you need to install and access the reports.

(c) Using SQL Server 2012 Performance Dashboard Reports

 After you have successfully installed Performance Dashboard Reports, you need to consider the following-

  • You need to ensure that all functions and procedures accessed by the queries in reports must be present in all instances of SQL Server for which you need to carry out performance analysis. You need to search for the ‘setup.sql’ file after navigating to the installation directory and run the script. Close the window after the execution is finished.

Go to Object Explorer in SQL Server Management Studio, right-click on the server instance, and select ‘Reports’ → ‘Custom Reports’. In the ‘Open File’ dialog box, select the ‘performance_dashboard_main’ report file and click ‘Open’. You will be displayed a warning dialog box stating that you are about to run a custom report. Click ‘Run’ in this dialog box to open the ‘performance_dashboard_main.rdl’ file.

Sample reports are pasted here for your further reference.
ImageImageImage

Happy Learning.

Cheers!
Aman Kharbanda

Advertisements

SQL Server Upgrade Failure with Script Execution Mode Errors

Encountered below specified issue during the MSSQL 2008 R2 (Enterprise 32-bit) in-place upgrade, the error details along with the workaround to fix the same has also been given in step by step.

 

Error:-
Image
Image

The error shown in the ‘….\Microsoft SQL Server\100\Setup Bootstrap\Log’ after the failure was as follows –

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

 

Verified the SQL Server error log file, and noticed the below errors-

Error: 2601, Severity: 14, State: 1.
Cannot insert duplicate key row in object ‘dbo.syscategories’ with unique index ‘clust’.

 

 

Error: 912, Severity: 21, State: 2.
Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 2601, state 1, severity 14. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

 

 

Error: 3417, Severity: 21, State: 3.
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

 

Workaround /Resolution:-

 

As per the SQL error log, it was clear that the insert was failing on the object ‘dbo.syscategories’ of msdb during the upgrade completion and the same was preventing the entire SQL Server instance from starting (and no SQL agent service was created after failure).

 

Ran the REPAIR option from the ADD/REMOVE Programs wizard of the Control panel, but same result.

 

Stop the script execution or bring SQL Server out of the Script Execution Mode –
Fixed the trace flag
“–T902” along with “–m” option in the SQL service startup parameter to prevent the script execution (i.e., to prevent the insert on the object
‘dbo.syscategories’ of msdb).

With the introduction of this trace flag, script execution mode was skipped and I was able to bring up the SQL Server services.

Followed below steps to delete the data in the
‘dbo.syscategories’ of MSDB-

 

 

 

> Sqlcmd – E

 

>USE msdb

 

>GO

 

>DELETE FROM msdb.dbo. syscategories

 

>GO

 

             (deleted all the rows)

 

>EXIT

 

           

 

Removed the trace flag “–T902” along with “–m” option in the SQL service startup parameter and the SQL database service this time has come up normally and available to query. Now ran the REPAIR option from the ADD/REMOVE Programs wizard of the Control panel and the upgrade was completed successfully without issues and SQL agent service was also got created automatically.

Hope this helps.

Cheers!
Aman Kharbanda

 

 

 

 

 

 

Rebuild / Restoration of System DB’s in SQL Server

Prerequisites of restoring the system databases is that the new SQL Server version needs to be exactly the same as the old one which was backup –

a) Master database :

1)         Start SQL Server in single user mode.
NET START MSSQLSERVER /m

2)         Connection to the SQL Server 2005. In this case, it is a default instance.
sqlcmd -E -S localhost

3)         Recover master database
restore database master from disk = ‘c:\bakcup\master.bak’

4)         If you get a success, then you should get message as below and the shutdown will stop automatically.
Processed 360 pages for database ‘master’, file ‘master’ on file 1.
Processed 4 pages for database ‘master’, file ‘mastlog’ on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

5)         Start the SQL Server. Suppose the msdb,model,tempdb location have not changed during the new installation, you will get the SQL Server with the original master databases.

If the installation/file path has changed for the new instance, then scroll down for remaining steps.

b) Msdb database :

1)       Shutdown the SQL Server Agent
2)       Restore the msdb database.
restore database msdb from disk = ‘c:\backup\msdb.bak’ with replace

c) Model database :

1)       Restore the model database
restore database model from disk = ‘c:\backup\model.bak’ with replace

After you get a final success, restart the SQL Server to check the errorlogs for if any errors happened. And after that, restore the user databases from backup.

When you are restoring the master databases, there might be chances that the installation/file path has changed for the new instance. For example, originally the system databases locates at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data but now it is C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data

After you restore the master databases, you will not be able to start the SQL Server.
Let’s start from master database step#5 again for this circumstance.

a) Master database
1)         Start SQL Server in single user mode.
NET START MSSQLSERVER /m

2)         Connection to the SQL Server 2005. In this case, it is a default instance.
sqlcmd -E -S localhost

3)         Recover master database
restore database master from disk = ‘c:\bakcup\master.bak’

4)         If you get a success, then you should get message as below and the shutdown will stop automatically.
Processed 360 pages for database ‘master’, file ‘master’ on file 1.
Processed 4 pages for database ‘master’, file ‘mastlog’ on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

5)         Start the SQL Server with trace flag 3608
net start MSSQLSERVER /f /m /T3608

6)         Connect to the SQL Server use command line again.
sqlcmd -E -S sql2005

7)         Use statements below to change the file path of the system databases –
alter database mssqlsystemresource modify file (name =data, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\mssqlsystemresource.mdf’);
go
alter database mssqlsystemresource modify file (name =log, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\mssqlsystemresource.ldf’);
go

–If you get a success, message will be as below.
The file “data” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “log” has been modified in the system catalog. The new path will be used the next time the database is started.

 alter database msdb modify file (name =MSDBData, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf’);
go
alter database msdb modify file (name =MSDBLog, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf’);
go

–If you get a success, message is gotten as below.
The file “MSDBData” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “MSDBLog” has been modified in the system catalog. The new path will be used the next time the database is started.

alter database model modify file (name =modeldev, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf’);
go
alter database model modify file (name =modellog, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modellog.ldf’);
go

–If you get a success, message is gotten as below.
The file “modeldev” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “modellog” has been modified in the system catalog. The new path will be used the next time the database is started.

 alter database tempdb modify file (name =tempdev, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\tempdb.mdf’);
go
alter database tempdb modify file (name =templog, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\templog.ldf’);
go

–If you get a success, message is gotten as below.
The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.

8)         Shutdown the SQL Server again and then start it.

net stop MSSQLSERVER
net start MSSQLSERVER

b) Msdb database-

1)       Shutdown the SQL Server Agent
2)       Restore the msdb database with new file location.
restore database msdb from disk = ‘c:\lab\msdb.bak’ with move ‘MSDBData’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf’,
move ‘MSDBLog’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf’, replace

c) Model database-

1)       Restore the model database with new location
restore database model from disk = ‘c:\lab\model.bak’
with move ‘modeldev’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf’,
move ‘modellog’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modellog.ldf’, replace

After you get a final success, restart the SQL Server to check the errorlogs if there are any errors reported. And after that, restore the user databases from last available backups.

Hope this helps.
Cheers!
Aman Kharbanda