Tag Archives: fix

How to determine why a T-SQL command is unreasonably slow

If you’ve ever found yourself in the situation were a command executing against a small table is nowhere near instant there can be numerous reasons for this but the most common causes are locks and waits.

The first step in identifying the problem is to execute the script below in a new query window while the troublesome command is running.

/* Queries Not Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status <> 'running';

/* Queries Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status = 'running';

 

This script will return two lists of the currently active sessions along with the stats associated with their execution. The first list will contain all the active sessions that are not running. The second list will contain all the active sessions that are running and will likely not contain the troublesome query you’re dealing with.

Identify your session based on the SqlText field. Be sure you’ve identified the session correctly as you may decide you want to kill the process later and killing the wrong one could cause you a lot of trouble.

  • status : If the status is not running look to the other fields in the returned result set to help identify the problem. If the session is in the running result set but you are unhappy with the performance it is likely the T-SQL needs to be optimized to make it run faster. This is a very broad topic and there are tons of articles and guides on the internet dealing with it.
  • blocking_session_id : If another session is blocking yours from executing, e.g. it has locked a table your command needs to write to, then this field will include the Id of the session causing the table to be locked. You can use EXEC sp_who2 to assess if the underlying command/query is experiencing a problem. If you are familiar with the blocking session you may know that you are able to kill the session without incurring any negative consequences. You can use the following code snippet to kill the blocking session.
    KILL blocking_session_id /*replace by the actual Id*/

    NOTE: Before you kill anything if it’s a command that has been running for a very long time it will likely take at least the same amount of time to roll back and unlock the table. You might be better off waiting for the session to finish on its own.

  • wait_type : If no blocking session is available, then the query is waiting for something, e.g. server resources etc. More details about wait types can be found HERE
  • wait_time : This stat value is measured in milliseconds. Short wait times are fine, specially in PAGEIOLATCH wait types (access to physical files) but longer wait times indicate a more serious problem.
  • last_wait_type : Indicates if the last wait type was different. This is quite helpful in analyzing if the query was blocked for the same reason before.

 

How to solve the SQL Server error ‘String or binary data would be truncated’

The ‘String or binary data would be truncated’ error will occur if an insert or update statement is trying to put too many characters into a field, defined in a table, which has been assigned too few character spaces. For example trying to write an email address with 255 characters into a table where the column email has been assigned 40 characters.

The easy fix is assign more characters to the column or columns you have determined are experiencing the problem. The more complicated but potentially necessary fix might be to change the logic or introduce validation at the source of data entry.

Finding the columns experiencing the problems however can be time consuming.

( . . . without the little script below of course)

SQL Server will kindly direct you to the stored procedure or insert/update statement that is experiencing the problem. However it will not pin point the exact column or columns that cannot be written to. The pain then is determining where the data won’t fit.

To speed things up take the entire query or query section you know to be causing the problem and write the results it into a temp table called #temp, i.e. SELECT * INTO #temp FROM SomeTable

Once the data has been written to the temp table #temp run the scrip below in the same window.

DECLARE @sql VARCHAR(MAX)

SET @sql = (
		SELECT (
				SELECT ',MAX(LEN(' + NAME + ')) AS [' + NAME + ']'
				FROM tempdb.sys.columns
				WHERE object_id = object_id('tempdb..#temp')
				FOR XML PATH('')
				)
		)
SET @sql = 'SELECT ' + RIGHT(@sql, LEN(@sql) - 1) + ' FROM #temp'

EXEC (@sql)
This will output results giving you the max character length of each field.
You can then compare these results to the defined destination table that the data could not be written to.
The source of the error will be where the max character number is greater than the assigned character spaces on the destination table.
For example the last time I used this query it easily highlighted that an agent had written a customers full address to the county name field which had a limit of 30 characters.

 

How to fix an SSRS Report that cannot find stored procedure fields or parameters while displaying the define query parameters window

When setting up your data sets in an SSRS report if you are using a complicated stored procedure, i.e. a SP which relies on dynamic SQL, temp tables or finishes with an IF statement, chances are the SSRS report will not be able to figure out what the SP returns. When this happens you will not be able to populate the data set with data.

This happens because the execution plan of the SSRS software isn’t smart enough and won’t be able to determine what fields the SP creates and therefore will not be able to create a means of storing the data on the report end.

Subsequently you’ll see the, often misleading, table below popup.

 

SSRS Pop up Define Query Parameters

The solution to stop this from happening is quite simple, but considering how expensive this software is it’s a solution that shouldn’t have to be employed.

The solution is to trick the SSRS software by simplifying the SP. Basically perform a select on the specific fields you need with no additional logic or create a table with the exact fields you need with corresponding data types and select from that.

Use this dumb SP to populate the dataset in the SSRS report.

In the “Choose a data source and create a query” window as below, click refresh fields.

SSRS window refresh fields
The software should now pick up the fields.
Change the SP back to the way it was before and do not refresh the fields again in the SSRS software and the fields should continue to populate as you need them.