Show Database ID in SQL Server

 The query below can retrieve database id by supplying the database name

-- Show database id using system view

 SELECT database_id FROM sys.databases

 WHERE name = '<db_name>';




-- Show database id using function

SELECT DB_ID('<db_name>');




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

EXECUTION PLAN

 What is execution plan and explain it? 

Execution plan graphically displays the data retrieval methods chosen by SQL Server. It represents the execution cost of specific statements and quires in SQL Server. This graphical approach is very useful for understanding the performance of the query. 

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by SQL Server Query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure science the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the query drop-down menu). If this option is tuned on it will display query execution plan in separate window query is ran again.

Execution plan displays:

1. Physical operations

2. Logical operations

3. Actual Number rows

4. Estimated I/O cost

5. Estimated CPU cost

6. Number of Executions

7. Estimated Number of Executions

8. Estimated Operator cost

9. Estimated Subtree cost

10. Estimated Number of Rows

11. Estimated Row Size

12. Actual rebinds

13. Actual rewinds

14. Key lookup

15. Nested look up

16. Index seek

17. Index scan