Category Archives: Uncategorized

Address High CPU on SQL Server

Following are the guidelines to troubleshoot High CPU scenario in MSSQL Server and fix the problem. 1. Identify running SQL statements taking more CPU 2. Find sql_handle, using SQL handle find average execution time in ms, Number of executions, and … Continue reading

Posted in Execution Plan, Performance, SQL, SQL Server, Uncategorized | Tagged , , , | Leave a comment

Fastest way to fix MSSQL Database with Suspect mode

I had a situation where multiple databases (50+ Databases) changed to Suspect mode. Simple solution: (assume the DBs are in Server-A, Availability group and Server-B is configured as secondary server) * Fail over the availability group to Server-B * Resume … Continue reading

Posted in SQL, SQL Error, SQL Server, Uncategorized | Tagged , | Leave a comment

Design considerations for SQL Server performance

While designing SQL Server for high performance following are the options to consider: 1. Block size to 64 KB (Defauly is 4 KB) Ref: * https://community.toadworld.com/platforms/sql-server/b/weblog/archive/2016/09/14/sql-server-storage-reading-block-size * https://blog.purestorage.com/what-is-sql-servers-io-block-size/ 2. Default Auto-Growth: change it to 1 GB 3. Validate MAXDOP Settings … Continue reading

Posted in Performance, SQL, Uncategorized | Tagged , , | Leave a comment

How to operate multiple objects in SSMS (F7)

While working on a service restoration, we had to resume data movement on 100+ random databases. Generating TSQL is the best option but UI will be very quick and reliable. Biggest challenge with SSMS UI will be to perform the … Continue reading

Posted in SQL, SQL Server, SSMS, Uncategorized | Tagged , , | 2 Comments

Frequently used MSSQL Commands

 

Posted in commands, SQL Server, Uncategorized | Tagged , , , , , | 1 Comment

Best practices being DEVOPS in IT

Index: MSSQL Index naming recommendations:   PK_ for primary keys UK_ for unique keys IX_ for non clustered non unique indexes UX_ for unique indexes <index or key type>_<table name>_<column 1>_<column 2>_<column n>  

Posted in SQL Server, Uncategorized | Tagged , , | 1 Comment