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 executing the query and analysing the Execution Plan

---Normally I’d run a statement and allow SSMS to iterate through the resultset 

select name,id,xtype from sys.sysobjects

--push the results into local variables 

declare @name nvarchar(128),@id int,@xtype char(2)
select @name = name,@id = id,@xtype = xtype from sys.sysobjects

 Read More

What is SQL Server Query Tuning? - SQL Server DBA

SQL Server – Find high impact queries with sys.dm_exec_query_stats

Open SSMS without the splash screen - SQL Server DBA

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 *
FROM ::fn_dblog( null , null)

 

Read More

SQL Server - Open SSMS on the Command Line - SQL Server DBA

SQL Server – Open SSMS without the splash screen graphic - SQL ...

SQL Server – Ctrl+Shift+N to Script Action

When making a SQL Server configuration change – I  prefer to produce code rather than complete through the SSMS GUI.  There are a number of benefits for producing the  code as opposed to completing the task through SSMS

1)     In a Production environment , most change request systems require the code attached to the change request. This allows an approver to check the code

2)     If troubleshooting is required  , sharing the code with other developers\DBAs is easier for them to understand the problem. Don’t forget , if posting to a forum – don’t include any sensitive data

If you don’t want to write your own code – you can simply use the Ctrl+Shift+N  , instead of clicking the OK button.

  

Read More

 

SQL Server - Open SSMS on the Command Line - SQL Server DBA

SQL server 2008 R2 – Silent installation for the SSMS and client tools

SQL Server – Open SSMS without the splash screen graphic - SQL ...

How to request SQL Server troubleshooting

SQL Server - Clear list of server names on SQL Server Management Studio

Sometimes there is a requirement to clear down the Server Name items on the SQL Server Management Studio Connect to Server GUI. 

 The method used is slightly different for versions of SQL Server Management Studio. These are undocumented processes and there is guarantee they are available in future versions. It’s unclear what other data is deleted.

 

 How to clear the list

Shut down all SSMS instances on the server
Delete/Rename the file
Open SSMS

 
 

For SQL 2005, delete the file:

C:\Documents and Settings\\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

For SQL 2008, the file location, format and name changed:

C:\Documents and Settings\\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin




For Windows 7, it's in the following:

SQL 2005:
C:\Users\\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

SQL 2008:
C:\Users\\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

 Related Posts

SQL Server - Open SSMS on the Command Line

SQL Server – Open SSMS without the splash screen graphic

SQL Server - SQLCMD with screen output and output file

A developer asked me to develop a template for executing SQLCMD with the output to the screen and output file. Normally it’s one or the other , but there is a workaround that allows SQLCMD to screen output and redirect output to file

Redirecting SQLCMD output to file is done  with the –o switch.  When using the –o switch  , the output that normally would go to the screen , is redirected to the output file.  On this occasion the developer wanted to both redirect the output to screen and the log file.

The purpose was to build in some interactivity with PAUSE , and make decisions on whether to continue based on the screen output.

The workaround to use the –o switch AND view the output on the screen is to use the the DOS TYPE command. The TYPE command  displays the contents of a text file.

 

SQLCMD -E -S MyServer\MyInstance  -i  "mysqlscript.sql"  -o output.log
:: The next step outputs output.log to the screen 
echo The query output was: 
TYPE output.log

 Related Posts

Powershell : SQLCMD and invoke-expression

SQL Server DBA Tools

SQL Server – Open SSMS without the splash screen graphic