SSMS Query Options – Discard results after execution

During SQL Server performance tuning consultancies one of the goals is to recreate the problem in the actual environment. In many cases this is not always possible. You may be sent a query that is experiencing difficulties, but you may not be allowed to use the client application . In these cases I may need to use SQL Server Management Studio (SSMS) One of the benefits in using SSMS is the various Query Options available. An option I use frequently when sql performance tuning is the Discard results after query executes. If the query returns a large recordset resources are... Read more →

Top 4 Best SQL Editors

Based on a recent poll I took the best\most used SQL Editor goes to : SQL Server Management Studio – Query Explorer This is not a surprise as it ships with SQL Server and is the default client . Quite often developers will remain with the tools they first started on. It’s good to maintain an open-mind, there may be more productive methods available. One factor which will influence sql editor tool choice is the range of platforms. For example, if a developer or DBA is writing sql code to support multiplatforms such as SQL Server and DB2 , they... Read more →

How to Calculate the size of sql results in KB?

Question: I’d like to understand if there is a way in SQL Server to estimate the size of a sql result set in kilobytes (KB)? Is there some sort of statistic returned which associates a size statistic to a result set? Answer: The first thing is to consider is that size is not a property of data. This means we need to use various calls to methods which supply the data. SQL Server Client statistics returns data which will supply you with various details. One of the details it supplies is “Bytes received from server”. To access Client Statistics ,... Read more →

SQL Server – SSMS query tuning – simulate the application

I execute queries in SQL Server management studio for query tuning– showing a return time different to other client applications. It’s difficult to know whether it’s the database engine or SSMS which might be causing a problem. Where is the delay , is it the database engine or is t SSMS? As a DBA it is difficult to isolate just the query. A simple technique to use is to return the resultset into local variables. This does a fairly decent job of discounting the SSMS application cost of iterating and displaying the recordset. This way you can focus just on... Read more →

SQL Server – Hover mouse over "*" sql statement to view all columns

When returning a sql recordset with a large amount of columns using SSMS , a useful feature is to hover over the “*”. Hovering over the “*” creates a vertical, comma – delimited list. Scan the list to find the columns required. The SQL Server maximum capacity for columns in a SELECT statement are 4096 – for 32 bit and 64 bit. It’s easy to see that a large amount of columns using the SQL Server Grid or Text Result can lead to some frustration. An example of a recordset with a large amount of columns is : SELECT *... Read more →