Check for CPU Pressure in SQL Server

 The following query shows resource wait time and signal wait time both in value and in percentage. This is particularly useful in ascertaining if there is CPU pressure in your SQL Server. A higher signal wait time percentage can be a sign of CPU pressure or need for faster CPU on the server.

select

SUM(signal_wait_time_ms) as total_signal_wait_time_ms,

SUM(wait_time_ms - signal_wait_time_ms) as resource_wait_time_ms,

SUM(signal_wait_time_ms) * 1.0/SUM(wait_time_ms) *100 as signal_wait_percent,

SUM(wait_time_ms - signal_wait_time_ms) * 1.0 /SUM(wait_time_ms) * 100 as resource_wait_percent

from sys.dm_os_wait_stats

SQL Server Database migration checklist

Pre Migration Checklist 

• Analyze the disk space of the target server for the new database.
• Confirm the data and log file location for the target server.
• Collect the information about the Database properties (Auto Stats, DB Owner, Recovery Model, Compatibility level,etc).
• Collect the information of dependent applications, make sure application services will be stopped during the database migration.
• Collect the information of database logins, users and their permissions.
• Check the database for the Orphan users if any.
• Check the SQL Server for any dependent objects (SQL Agent Jobs and Linked Servers).
• Check, if the database is part of any maintenance plan.

Database Migration Checklist

• Stop the application services.
• Change the database to single user mode.
• Take the latest backup of all the databases involved in migration.
• Stop the SQL Services on live server.
• Copy the backup from live to destination server.
• Restore the databases on the target server on the appropriate drives.
• Cross check the database properties as per pre-migration checklist output.
• Execute the output of Login transfer script on the target server, to create logins on the target server.
• Check for Orphan Users and Fix Orphan Users. 
• Execute DBCC UPDATEUSAGE on the restored database.
• Rebuild Indexes ,As per the requirement.
• Update statistics.
• Recompile procedures.
• Configure Full backup, Log backup, integrity check, rebuild index jobs.

Post Migration Checklist

• Check the integrity of database.
• Start the application services, check the application functionality.
• Check the SQL Server Error Log for login failures and other errors

SQL Script: How To Find Index Fragmentation

What Is Index Fragmentation?

Over time, as records are inserted, updated, and deleted, your tables and indexes become fragmented.  This fragmentation can lead to poor performance of not only your SELECT queries, but also your INSERT, UPDATE, and DELETE operations.

How Do I Find Index Fragmentation?

Index fragmentation can be found by querying the built in sys.dm_db_index_physical_stats DMV. To get readable, useful information you’ll also need to join your query to other DMVs such as sys.indexes and sys.tables.  Below is a simple query that will provide a list of indexes, fragmentation percentage, and record counts for each table in a database.

USE YourDatabase GO SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, indexstats.page_count, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER BY indexstats.avg_fragmentation_in_percent DESC