Showing posts from March, 2020

Essential Trace Flags to enable at SQL Server Services startup

List of trace flags to enable on SQL server service startup:

Server NameTrace FlagStatusGlobalSessionPRD-MSSQL01C1222110PRD-MSSQL01C1224110PRD-MSSQL01C4136110

4136 will help in preventing parameter sniffing on sql server and in turn will result in query optimizer to create optimized plans for same queries having different parameters which will result in better performance. Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.
Scope: Global or session1224 disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation. This helps avoid "out-of-locks" errors when many locks are being used.
Scope: Global or session1222 helps in detecting deadlocks returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.
Note: Avoid using trace flag 1222 on workload-inten…

Part 3: List of Questions I have been asked in Interviews

How many synchronous and asynchronous nodes are possible in always on?What is RRE lookup?How do you resolve RRE lookup ?What are different types of joins available in SQL server and visible in SQL server execution plans?How many agents are there in replication? What are there names?What is the work of distribution agent ?Replication issues ? Primary key violation issues ? Row mismatched conflicts issues in replication?How do you find out the replication issues errors?How do you solve query timeout errors?Have you worked on mirroring?Have you worked on logshipping?What is a listener?(always on )Ola Hallengren's parameters ?Sample size in Ola Hallengren's scripts?What is the frequency that you are using for updating statistics?Have you worked on SSIS ?Like we have principal and mirror in mirroring ,publisher and subscriber in replication what do we have in terminology in always on ?What is better index seek or index scans?How do you validate that databases are healthy?Have you s…

Checklist: Before applying for adsense

Before applying for adsense below things has to be taken care of

Remove reference to all other old sites and turn of profile about widgets
Create a email
Create a google analytics account link it to your site
Create a menu structure
Fill ALT and Description tags for all posts and images
No follow external links
Place menu optimally and check its working fine and visible in all screen sizes
See available adsence widgets still has proper visibility after template modifications
Correct all grammatical mistakes
Create About, Contact and Privacy pages
Create few more Posts
Work on SEO a bit for few days
Add links to Youtube channel

Assessment for configurations on MSSQL instances

Here are the list of things that I think are misconfigured on SQL Server and should have been gone through RTPA process when they were configured:

1) Max degree of parallelism :

This configuration tells SQL Server how CPU’s should be utilized for parallel processing,
so here is the thing we have multiprocessor servers, we are paying license cost for SQL Server based on cores but are we using multiprocessing ?

No, Which means lag in performance, sure this might result in blocking sometimes but it’s worth, looking at the performance gain that you can get.(Ever wondered why you were not getting blocking issues on PRD nodes irrespective of transactions it handles? Guess what this value set to 1 is answer which is at cost of loosing parallelism)

Note: Setting max degree of parallelism to 1 suppress use of parallel plans and here is the situation in 3 of out Prod instances out of 5 btw rest 2 prod instances also have this value set to 2 even when all these 5 instances have 4 CPU available:


Performance improvements : Gaining performance by adding SET NOCOUNT ON in stored procedures, triggers and using Qualified object names in dynamic sql

Hi Team,

I was looking at articles for improving performance on sql server and here are few things that we might be missing in our environment and could help in gaining some extra kick.


I have verified by scripting few stored procedure none is using SET NOCOUNT ON using this option suppresses the number of rows effected messages which are returned after execution of all inserts selects deletes updates. Its fine when you are having these on ssms but when you are executing sp from app this small bit of information can really gain some performance by avoiding sending this data over the network.

Note: In case you need to have the rows affected you can still use @@rowcount to get that information

It can be done by adding SET NOCOUNT ON just after AS clause in CREATE PROCEDURE STATEMENT.
In-fact in new versions of SQL SERVER SSMS the template has by default updated and introduced its usage:


Popular posts from this blog

Enabling fixed Sidebar for blogger Contempo template for screen sizes smaller than 1440px

Adding copy to clipboard button to every google code prettify pre blocks in blogger

Solution: More Posts & Posts Links not working with Adsense Anchor ads on Touch screen mobiles in blogger

Query to Expand and List All logins within a SQL Server Group

Powershell script to automatically send HTML emails using gmail smtp

Adding code prettify to Blogger