Tag Archives: SQL Server

What are default Index Maintenance settings in Ola hallengren maintenance solution

Summary: When Fragmentation < 5%, No maintenance When Fragmentation >= 5% and Fragmentation < 30 %; index ReOrganize When Fragmentation >= 30%; index Rebuild (If Supported online or Offline) Note: This is the default and regular scenario. check reference for … Continue reading

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

Validate and delete duplicate records from MSSQL table

I had a situation where Index creation failed and business wants to delete duplicate entries immediately from a specific table. I had to validate the columns which are unique and which had different values. Following is easy script to validate … Continue reading

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

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

SQL latest available patch reference

Best source for latest SQL Server patches: https://sqlserverbuilds.blogspot.com/ Ref 2: https://technet.microsoft.com/en-us/library/ff803383.aspx (Hotfix information is not updated) Option 3: https://sqlserverupdates.com/

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