Select Statement

Transact-SQL
1

Hi,

The following Query is hindering processing with Executions/min = 11035878, CPU (ms/sec) = 8922441.
SQL Server ranked it as the most expensive query... How can I update this improve performance...

SELECT
				@ret_val 
					= 
						CAST(f.VersionNumberMajor as varchar(5)) 
					+ 
						'_' 
					+ 
						CAST(f.VersionNumberMinor as varchar(5))
					+
						'_'
					+
						CAST(f.VersionNumberRevision as varchar(5))
			FROM
				PROCESSING.CollectionFile f WITH (NOLOCK)
			WHERE
				f.CollectionFileIdentifier = @CollectionFileIdentifier

Thanks

2

Perhaps don't run it 183,931 times a second!(?)

Probably the CollectionFile table is already clustered on CollectionFileIdentifier, but, if not, cluster that table that way.

3

Yes - CollectionFileIdentifier is PK and clustered by default.

4

We've run into various problems like this. It's usually caused by poorly written front end code that hits the database for the same thing many times for every action that the user makes on the front end. There are two ways to fix it...

  1. Is to find and correct the front end code. Of course, to a whole lot of developers, that's almost as complex as trying to find where a memory leak is. It's the best way to fix this problem but management will frequently not provide the time for such an investigation because they frequently don't get it and won't until performance absolutely tanks... which it eventually will.
  2. The other way is to cache that data on the WebServer once an hour or so and let the WebServer deal with the problem. Unfortunately, that will also involve developers and management perception.

That means that you'll need to prove the ROI in terms that they can understand. Figure out how much CPU, Reads, and Memory that it's using and explain how it's doing more reads per minute in the form of how many mega-bytes (or tera-bytes) it use in an hour and compare that to the size of the database. # of Reads/128.0 converts the reads to megabytes.