TSQL Archives - Page 4 of 5 - Performance Tuning And Infra Automation

Non – Trusted Foreign Keys in SQL Server

Today, I was working with one user to troubleshoot their query timeout issues. Analyzing wait stats on server, I found 'LCK_M_X' wait to be most prominent wait on server. This…

Ajay Dwivedi July 17, 2017

Errors TSQL

Easy Trick to Debug Long TSQL Codes

In my previous organization, working as Senior SQL Developer, I was often asked for help by fellow colleagues for identifying issues inside long and complex TSQL codes. Finding issues becomes…

Ajay Dwivedi May 6, 2017

TSQL

Create/Restrict Data Files – Automation

In my organization, as part of our strategy we keep Data files in 2 TB sized Mount Point Volumes named like 'Data','Data01','Data02' etc. on E:\ drive. Similary, for Log files…

Ajay Dwivedi April 16, 2017

Performance Tuning TSQL

Optimize for Value – TSQL Query Hint

Today, I was working on an incident of slow running procedure. Procedure was running fine for most of the parameter values but, was getting stuck for few values. I could…

Ajay Dwivedi January 3, 2017

TSQL

How do I verify if my login if set to Non-Expiry [SQL Server]?

Today I was working on multiple access/permission requests of same user on multiple servers. Due to previous poor experience using sql authenticated login set to expiry by DBA, he kept…

Ajay Dwivedi November 28, 2016

Execution Plan Performance Tuning TSQL

Query Optimization 03 – Users statistics (individual user) [StackOverflow]

Hi Friends, this is my 3rd blog post of series Query Optimization. In last blog post Questions and Answers per Month, we took optimization decision by analyzing warnings suggested by…

Ajay Dwivedi November 27, 2016

TSQL

Script to Analyze Mount Point Space & Generate Shrink File Statements

In my organization, we often receive far too many incidents for low free disk space. As part of resolution we have to check for unexpected files/folders on the mount point…

Ajay Dwivedi November 20, 2016

Errors Internals Performance Tuning

Performance Tuning With Predicate Logic

I have often seen Developers write predicate logic without bothering about performance. That works most of the time. But what about queries that return huge data sets? Is there any…

Ajay Dwivedi October 15, 2016

TSQL

Get Decimal Scale Based on Variable Value

In recent past, while designing a report mailer I faced a requirement where I had to convert a float column into DECIMAL with dynamic precision value coming from outside of…

Ajay Dwivedi October 9, 2016

TSQL

Query Shorcuts SQL Server SSMS – Get Max Data Length

There are multiple articles on internet regarding Query Shortcuts in SQL Server Management Studio. So I won't cover details of SSMS Query Shortcuts. But to increase your productivity, I will show…

Ajay Dwivedi September 30, 2016