Installing and configuring SQL Server 2012 Performance Dashboard Reports

Installing and configuring SQL Server 2012 Performance Dashboard Reports

The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. The reports allow a database administrator 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.

The information captured in the reports is retrieved from SQL Server's dynamic management views. 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 your server.

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

1. Ø  CPU bottlenecks (and what queries are consuming the most CPU)
2. Ø  IO bottlenecks (and what queries are performing the most IO)
3. Ø  Index recommendations generated by the query optimizer (missing indexes)
4. Ø  Blocking
5. Ø  Latch contention

To install this add-on please go through below link

http://www.microsoft.com/en-us/download/details.aspx?id=29063

Download & copy SQLServer2012_PerformanceDashboard.msi file into server where you want to install.

 

Double click on downloaded file to install SQL Server 2012 performance dashboard reports

 

Clink on run

Click on Next

 

Select I accept the terms in the license agreement  

And click on next

Enter your name and company. Click on Next button.

If you want change the default installation location click on Browse and provide appropriate location

Click on Next

Dash board report will install files in above location

Click on Install button

Click on finish button

To load reports – right click on server name and expand reports and click on Custom Reports

Select the .rdl file from below location shown as in below images

 

Click on Run button

It will give above error. You need to execute the setup.sql files located in installation directory.

Open the file in Query Analyser

 

Execute the script

 

After successful execution  please load the report by selecting .rdl file. It will show the report as below

 

 

Script to get the SQL Logins information

Login type descriptions

S = SQL Server user(SQL_USER)

U = Windows user(WINDOWS_USER)

G = Windows group(WINDOWS_GROUP)

A = Application role(APPLICATION_ROLE)

R = Database role(DATABASE_ROLE)

C = Certificate mapped(CERTIFICATE_MAPPED_USER)

K = Asymmetric key mapped(ASSYMETRIC_KEY_MAPPED_USER)

Using below script you can list all Login Accounts in a SQL Server

SELECT name AS Login_Name, type_desc AS Account_Type

FROM sys.server_principals 

WHERE TYPE IN ('U', 'S', 'G')

ORDER BY name, type_desc

Using below script you can list all  SQL Login Accounts (Not windows logins)  only in a SQL Server 

SELECT name

FROM sys.server_principals 

WHERE TYPE = 'S'

ORDER BY name, type_desc

Using below script you can list all Windows Login Accounts (Not SQL Logins)  only in a SQL Server 

SELECT name

FROM sys.server_principals 

WHERE TYPE = 'U'

Using below script you can list all Windows Group Login  (Not SQL Logins)  only in a SQL Server 

SELECT name

FROM sys.server_principals 

WHERE TYPE = 'G'

How to copy non-clustered indexes to subscriber in snapshot replication

In snapshot replication by default Non-cluster indexes are not copied to subscriber below is the work around for copying Non-cluster indexes to subscriber in Snapshot replication.

Step 1:

  - Login into the Publisher server

  -  Expand the Replication folder and Local Publications

  -  Right click on the publisher and select properties

Step 2:

                In Publication Properties window select Articles Tab à Select the Article Properties

               

Step 3:

  -  In Popup select Set Properties of All Table articles to copy all objects Non-Cluster indexes

  -  Select Set Properties of Highlighted table article to change the option to copy Non-cluster indexes of specific table

Step 4: 

                In Properties for All Tables Articles window change the below highlighted property from False to True.

               

In next run of snapshot will copy all noncluster indexes to subscriber.

Validate all views in the database

There is an sp that validates the metadata and the   same sp we can use to validate

set nocount on

select ' print ''' +name +'''

exec sp_refreshview ['+name+']' + CHAR(10)+ '

go '

from sys.views

Execute  the above script it will give script to run sp_refreshview on all views. Run the scripts generated using above sql statement I will give error link below screen

DBCC CHECKIDENT

-- Create Table Script to test CHECKIDENT

CREATE TABLE DBCC_ReSeed

( ID INT IDENTITY(1,1) NOT NULL,

DateAdded DATETIME NOT NULL )

-- Inserting values to DBCC_ReSeed table

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

SELECT * FROM DBCC_ReSeed

--- Check the current value which produces the below output… 

DBCC CHECKIDENT ('DBCC_ReSeed', NORESEED)

---  Output like :  Checking identity information: current identity value '5', current column value '5'.

--- Now reset the identity value to 20 so that the next time when we insert data into DBCC_RdSeed table , the value will be 21…

DBCC CHECKIDENT('DBCC_ReSeed', RESEED, 20)

----- Output like :   Checking identity information: current identity value '5', current column value '20'

--  Add another row and check identity value. The row inserted will have a value of 21… 

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

SELECT * FROM DBCC_ReSeed

-- Delete data in table and reset to start from 1

DELETE FROM DBCC_ReSeed

DBCC CHECKIDENT('DBCC_ReSeed')

-- After deleting data identity value didn’t reset and the value will be remain same  

---  Output like :  Checking identity information: current identity value '20', current column value '20'.

DBCC CHECKIDENT('DBCC_ReSeed', RESEED, -1)

---  Output like :  Checking identity information: current identity value '21', current column value '-1'.

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

SELECT * FROM DBCC_ReSeed

Recently executed query

Using below query you can get executed time and query text

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

Results of above query

Using below query you can find the Recently excuted query for specific SPID

SELECT SDEC.[most_recent_sql_handle], DEST.[text], last_read, Last_write

FROM sys.[dm_exec_connections] SDEC

 CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST

WHERE SDEC.[most_recent_session_id] = 52

Results of above query

Thanks

Venkat Sangu

Total user Database size in GB's

--- To fetch the User databases  Size in GBs use below query

SELECT    

DB_NAME(db.database_id) DatabaseName,    

(CAST(mfrows.RowSize AS FLOAT)*8)/1024/1024+(CAST(mflog.LogSize AS FLOAT)*8)/1024/1024 DBSizeGB

FROM sys.databases db    

LEFT JOIN (SELECT database_id,

                  SUM(size) RowSize

            FROM sys.master_files

            WHERE type = 0

            GROUP BY database_id, type) mfrows

    ON mfrows.database_id = db.database_id    

LEFT JOIN (SELECT database_id,

                  SUM(size) LogSize

            FROM sys.master_files

            WHERE type = 1

            GROUP BY database_id, type) mflog

    ON mflog.database_id = db.database_id    

        where db.database_id > 5

ORDER BY 1 DESC

---- Results are as below

Shrink Database Log file in SQL Server

 

Method 1 : This script will shrink the all log files of specific database by  change the recovery modle to simple  and again it rever back the recovery model

 

DECLARE @tbl TABLE

(

      ID INT IDENTITY(1,1),

      DBNAME NVARCHAR(1000),

      [FileName] NVARCHAR(1000)

)

  

INSERT INTO @tbl

SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles  ST INNER JOIN Sys.databases SB on  ST.dbid = sb.database_id where sb.name = 'TEST2008'  AND FileName LIKE '%ldf' and sb.state = 0

 

DECLARE @MinID INT , @MaxID INT,@DBName NVARCHAR(1000),@FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@SQL NVARCHAR(MAX)

 

SELECT @MinID = MIN(ID),@MaxID = MAX(ID) FROM @tbl

 

 

WHILE(@MinID <=@MaxID)

BEGIN

      SELECT @DBName = DBNAME,@FileName=[FileName] FROM @tbl where ID = @MinID

 

      SELECT      @RecoveryModel = recovery_model_desc FROM sys.databases where name = @DBName

 

            SELECT      @SQL= N'USE ['+ @DBName+']'+CHAR(10)+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT'ELSE N''END+CHAR(10)+

                  +N'DBCC SHRINKFILE('+@FileName+',1)'+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END

                  +CHAR(10)

      PRINT @SQL

      EXEC SP_EXECUTESQL @SQL

     

      SELECT @MinID = @MinID +1

END

 

Method 2: This script will shrink the all databases log files, depends on the size of log file by  change the recovery modle to simple  and again it rever back the recovery model

 

DECLARE @tbl TABLE

(

      ID INT IDENTITY(1,1),

      DBNAME NVARCHAR(1000),

      [FileName] NVARCHAR(1000)

)

INSERT INTO @tbl

SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles  ST INNER JOIN Sys.databases SB on  ST.dbid = sb.database_id where Size > 100000 AND FileName LIKE '%ldf' and sb.state = 0

 

DECLARE @MinID INT , @MaxID INT,@DBName NVARCHAR(1000),@FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@SQL NVARCHAR(MAX)

 

SELECT @MinID = MIN(ID),@MaxID = MAX(ID) FROM @tbl

 

 

WHILE(@MinID <=@MaxID)

BEGIN

      SELECT @DBName = DBNAME,@FileName=[FileName] FROM @tbl where ID = @MinID

 

      SELECT      @RecoveryModel = recovery_model_desc FROM sys.databases where name = @DBName

 

            SELECT      @SQL= N'USE ['+ @DBName+']'+CHAR(10)+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT'ELSE N''END+CHAR(10)+

                  +N'DBCC SHRINKFILE('+@FileName+',1)'+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END

                  +CHAR(10)

      PRINT @SQL

      EXEC SP_EXECUTESQL @SQL

     

      SELECT @MinID = @MinID +1

END

 

Kill all user SPIDs in a SQL Server Database

Using this script you can kill all the SPID connected to TEST2008 database 

 

 

 

DECLARE @DB SYSNAME 

SET @DB = 'Test2008'  -- Here you need to give the database name where you need to kill all connection

 

 

DECLARE @SPID VARCHAR(4), @cmdSQL VARCHAR(10)

 

IF OBJECT_ID('tempdb..#KilledSpidsInfo ') IS NOT NULL

BEGIN

DROP TABLE  #KilledSpidsInfo

END

 

SELECT * into #KilledSpidsInfo FROM master.dbo.sysprocesses

WHERE SPID > 50 

AND SPID != @@SPID 

AND DBID = DB_ID(@DB) 

 

select * from #KilledSpidsInfo

 

DECLARE cCursor CURSOR

FOR SELECT CAST(SPID AS VARCHAR(4)) FROM master.dbo.sysprocesses

WHERE SPID > 50 

AND SPID != @@SPID 

AND DBID = DB_ID(@DB) 

 

OPEN cCursor

 

FETCH NEXT FROM cCursor INTO @SPID

 

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmdSQL = 'KILL ' + @SPID

 

 EXEC (@cmdSQL)

FETCH NEXT FROM cCursor INTO @SPID

END

 

CLOSE cCURSOR

 

DEALLOCATE cCURSOR

 

change server level collation for a SQL Server Instance

Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales

The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases. The server collation is specified during SQL Server installation.  It is not mandatory that we change the default server level collation, because you can specify a different collation level when you create users databases, but you need to remember to specify this when creating user databases.

To change the default SQL Server collation you can simply rebuild the system databases. When you rebuild the master, the model, msdb and tempdb system database are actually dropped and recreated in their original location. If a new collation is specified in the rebuild statement the system databases are rebuilt using that collation setting. Any user modifications to these databases will be lost, so it is important to backup any of this information you wish to retain

Step 1

First check the existing SQL Server collation setting of your instance. Run the command below to get the collation value of your SQL Server instance.

SELECT SERVERPROPERTY('Collation')

Step 2

Make sure to record all server level settings before rebuilding the system databases to ensure that you can restore the system databases to their current settings.  

Step 3

Take backup of all user and server databases.

Take the backup of all jobs, maintenance plans, logins and their access levels

To take all jobs backup in OBJECT EXPLORER window select job folder & in right side OBJECT EXPLORE DETAIL window it displays all jobs, select all jobs and right click on your selection then choose the SCRIPT AS option to create the script for all jobs.

Next is to secure your logins, passwords and their access levels. You can use sp_help_revlogin stored procedure to create a script for all logins so they can be recreated easily. And take server level permissions using script

Step 4

Detach all user databases before rebuilding your system databases. If you leave databases attached they will be detached and will be found in the database folder.

Step 5

Now its time to rebuild your system databases. This operation will recreate your master database and all existing settings will be reset. Run the below command from a Windows command prompt. Make sure to run this command from the directory where you have placed your SQL Server setup files. Once you press enter, a separate window will appear to show you the progress bar. Once the rebuild is done, that window will disappear.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MANVENDRA /SQLSYSADMINACCOUNTS=domain\adminUser /SAPWD= SaPWD@SqlRebuild /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

* SQL_Latin1_General_CP1_CI_AI –this is the new collation need to change

Once the rebuild operation is complete, check the server collation to verify whether this change is successful or not. As we can see in the screenshot below, the server collation has changed to SQL_Latin1_General_CP1_CI_AI. At this point we cannot restore any of the system databases, because doing so will revert back to the previous collation setting. So we will need to use the scripts that were created to recreate logins, jobs

Step 6

Attach all user databases which were detached in Step 4.

Step 7

Now change the collation settings of all user databases. It's not necessary to change the collation settings for the user databases, it totally depends on your requirement.

Run the commands below to change the collation settings of your user databases.

ALTER DATABASE CollationChangeDBName collate SQL_Latin1_General_CP1_CI_AI

Sometimes the command fails to execute and you get this error:

Msg 5075, Level 16, State 1, Line 1 The object 'PK_xxxx' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

In that case you may need to export all data and recreate the database with the new collation settings.

Step 8

Now run all of the scripts which were created in Step 3 to restore jobs, alerts, logins, operators, etc...  Also don't forget to change the server level configuration settings which were captured in Step 2.

Now your instance is ready to use the new server level collation.