Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again executed with in 11 sec.Then i tried removing cast used in query and when executed it ran just in 8 seconds.But when i again the original query it takes less time.I checked in several computers running the query.But in some cases i get output just in 1 or 2 seconds also.So there is no consistency with the time taken to execute.So i am not able to find why this happens?

Below is the query i used to test

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE ( Charindex('9000413237',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0 ) 

Here customer table has about 30 columns.In that 'Customer name' is of datatype varchar(255),'Sl_No' is of int,'Id' is of int and 'Phone no' is of varchar(255).

when i execute the same query(with cast) time required to execute is different even in same PC for different trials.It varies from 1 second to 14 second(i tried number of trials executing query). The variation in time may be due to network lateny,cache,table in high usage by other user etc. But is there any tool or method by which i can find the reason for the time taken(may be slow or fast) to execute?

update I just tried to check IO stalls by using the query

SELECT 
    mf.physical_name,
    ( io_stall_read_ms / ( 1.0 + num_of_reads )) as [avg read wait],
    ( io_stall_write_ms / ( 1.0 + num_of_writes )) as [avg write wait],
    i.name,
    fs.io_stall
    FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]
    INNER JOIN sys.databases as i on fs.database_id = i.database_id
order by i.name desc

In the output my database 'company' was taking high io stall.below is the values i got for that database.(The original output had iostall of all database files)

avg read wait-37.17162364 avg write wait -20.66666667 io_stall-4813081 So the database i used in query had high iostall .What is the reason for it?

I added Phone no as non clustered index and just rewrite the query as

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [company].dbo.[customer]
  WHERE [company].dbo.[customer].[Phone no]  LIKE '%9000413237%'

Now the query executes within 1 second. I cant use LIKE '9000413237%' as i require phone number to be searched at any part of the column.So i hav to use LIKE '%9000413237%'.But in several articles it is mentioned that using like '%abc%' will not benefit indexing only 'abc%' can benefit from indexing. But in my case using LIKE '%9000413237%' helped me a lot with indexing.So i have doubt that why people say using LIKE '%9000413237%' does not benifit

share|improve this question

closed as unclear what you're asking by Paul White, dezso, Mark Storey-Smith, Marian, Mikael Eriksson Jul 23 '13 at 10:41

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
Why varchar(255)? Why not 256? or 100? or nvarchar? And, more importantly, why do you expect something to run in the same time on different machines? –  dezso Jul 23 '13 at 10:25
3  
Why CHARINDEX and why CAST to VARCHAR(MAX)? Am I missing a key detail that prevents you from using plain old LIKE '%9000413237%'? –  Mark Storey-Smith Jul 23 '13 at 21:38
    
Can you, please, add the actual execution plan of the query (maybe from different machines, to see if there are plan differences)? That would be helpful to see if there are similar plans, but different resources, or maybe different plans compiled. –  Marian Jul 24 '13 at 8:38
3  
@dezso: when you see varchar(255) in MSSQL it usually comes from the database having been upgraded from MS Access at some point in its history (or the database's creator being more familiar with Access+Jet than SQL Server). The old Jet backend for Access limited the basic text columns to a maximum of 255 characters (presumably they were stored as a 1-byte length indicator followed by the character data). –  David Spillett Jul 24 '13 at 11:45
3  
@DavidSpillett thanks, one of the big mysteries in life is solved. –  dezso Jul 24 '13 at 11:48

1 Answer 1

One possible explanation:

The first time you run your query it's probably not in cache, which means it has to be read from disk and stored in memory. If your query remains the same and execution plan doesn't change, the time it takes to execute the same query will be less.

If the SQL Server you are using is on your own computer and/or others will not be affected, you can clear the plan cache with the DBCC FREEPROCCACHE to see if the execution times remain constant.

You can check to see which queries are in cache by running the following command

SELECT text,objtype, refcounts, usecounts, size_in_bytes,
    disk_ios_count, context_switches_count, pages_allocated_count, original_cost,
    current_cost
FROM sys.dm_exec_cached_plans p
    CROSS APPLY sys.dm_exec_sql_text(plan_handle)
    JOIN sys.dm_os_memory_cache_entries e
    ON p.memory_object_address = e.memory_object_address
WHERE cacheobjtype = 'Compiled Plan'
ORDER BY objtype desc, usecounts DESC

If you see the query

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE ( Charindex('9000413237',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0 ) 

Then you know its in cache.

If clearing out the cache doesn't make the execution times more consistent, please include the actual execution plan, by selecting CTRL+M before executing your query.

Another possible explanation: Since you are testing from different computers, there could be additional network latency involved when SQL Server sends the data back to the computer you are testing from. You can verify this by using task manager and opening up the Networking Tab when running your query from another server.

Update 15:26

If bandwith was an issue , one solution would be to find out: 1. How much bandwith exists between your clients and server 2. How much bandwith you are actually using

Your network administrator or sysadmin can tell you how much bandwith exists between you and other machines within your domaine. Using Process Explorer or perfmon, you can easily determine how much of that bandwith is being used by SQL Server and other processes. Just keep in mind that when you are executing a query on the server, the results don't have to travel across a network cable as they would with a remote client, which would change execution times.

Your inital question though is why you are seeing inconsistent execution times for the same query. So a discussion about networks and bandwith would be off topic.

Update 09:27

Here are two screenshots of Process Explorer that will help you find the graphs that will allow you to analyze, CPU,IO,Memory and Network traffic.

Main Screen

Main Screen

Graphs

Graphs

share|improve this answer
    
Thank u for the nice explanation. Can you please tell me how can i find the reason for slow running of query.(may be due to network latency,table in high usage etc etc).Is there any method to find the reason for it? –  IT researcher Jul 23 '13 at 12:52
    
Hello @ITresearcher, your question is on hold. Please make the necessary corrections and I will try to provide further help once the question is no longer on hold. –  Craig Efrein Jul 23 '13 at 13:13
    
Technically, you can edit your answer regardless of whether the question is on hold or nor (in case you were not aware). It is new answers that are prevented by the "on hold" status. –  Andriy M Jul 23 '13 at 13:19
1  
@ITresearcher, I would suggest including the execution plan in your question. I will update my answer in the mean time with a link to Process Explorer that I use to troubleshoot individual processes in Windows. –  Craig Efrein Jul 23 '13 at 13:24
    
@CraigEfrein Please see my updated question. I know i can use indexing by modifying query etc which can benefit the performance.How can i know what is the reason for different time shown for executing.I want to know exactly which factor has effected etc.I have already edited question.What more i should include now? –  IT researcher Jul 23 '13 at 13:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.