Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


Archive for November 22nd, 2008

Good SQL Server 2008 training videos

Posted by decipherinfosys on November 22, 2008

In the latest issue of the SQL Server Magazine, there was an attachment for some of the training videos which pointed to this site:

and when you go there, it is a re-direction to the ISV Innovation site:

Over there, you will get 8 different video tutorials. In case you have not gone over those yet, take a look – they are very well done and gives you a lot of very good information about the new feature sets of SQL Server 2008. These are the 8 tutorials:

1) Uncovering T-SQL on SQL Server 2008.
2) Working with unstructured data in SQL Server 2008.
3) Working with asynchronous data in SQL Server 2008.
4) Deploying managed code to SQL Server 2008.
5) Developing for SQL Server 2008 using Visual Studio.
6) SQL Server 2008 and ADO.Net Entity Framework Integration.
7) Communicating with SQL Server 2008 using HTTP.
8 ) Working with SQL Server 2008 and Disconnected Clients.

Posted in SQL Server, Technology, Visual Studio | 3 Comments »

Query Execution Time outs because of lack of memory

Posted by decipherinfosys on November 22, 2008

We have discussed the different kind of timeouts before and have also covered how one can prevent a runaway query by using some simple options. You can read those posts here and here. In today’s post, we will cover the query execution time outs that you might have seen at times in busy systems which have an error like:

[State:42000 Error:8645] [Microsoft][SQL Native Client][SQL Server]A time out occurred while waiting for memory resources to execute the query. Rerun the query.

If you are new to SQL Server memory management and would like to get a good understanding of it, here is a MSDN post on that topic.  If you remember from this post, the steps performed (at a very high level) when a query gets submitted to the engine are parsing, compilation and then execution.  Prior to the parsing, if the execution plan is already in the cache, then the engine selects it.  If it is not in the cache, then it parses it to prepare a sequence tree and that is then converted into algebras tree to generate the algebraic plan.  The execution of the query depends upon whether enough memory is available from the buffer pool to satisfy the requirements of that query.  If enough memory is not available, then the query is put into a queue with a timeout value and has to wait for the memory to become available.    This timeout value is based off the  estimated query cost – the higher the query cost, the higher the timeout value.  So, if enough memory is not available prior to hitting that timeout value, that is when you will see the error message that is shown above.

When the error occurs, the query is also removed from the queue.  This raises an interesting question which a client DBA had asked us – so, if there are already quite a few queries in the queue and a new query is submitted for which there is enough memory available for the execution, how does the queue work?  Haven’t researched that completely yet but our understanding is that the ranking in the queue is a combination of the cost and the wait time.  The lower the cost or the higher the wait time, the higher the ranking but am not sure about that – have to test that out or post a question on the MSDN forums.

However, while looking into the DMVs, we did see this DMV: sys.dm_exec_query_memory_grants which provides us with the information pertaining to the queries that have acquired a memory grant or are in a waiting status.  The column “is_next_candidate” can be used to find out whether the query has already been granted memory or whether it is a candidate for the next memory grant.  Here is a simple query on this DMV to get information on those queries which have not been granted memory and are waiting:

select *
from sys.dm_exec_query_memory_grants
where is_next_candidate in (0,1)
order by is_next_candidate desc, queue_id, wait_order;

Also, we can use the CROSS APPLY operator to get this DMV joined to sys.dm_exec_query_plan in order to get the execution plan for the query as well as with sys.dm_exec_sql_text to get the exact query:

SELECT  sdeq.*
,db_name([dest].[dbid]) as database_name
,object_schema_name([dest].[objectid], [dest].[dbid]) as [schema_name]
,object_name([dest].[objectid], [dest].[dbid]) as [object_name]
FROM    sys.dm_exec_query_memory_grants sdeq
CROSS APPLY sys.dm_exec_sql_text(sdeq.sql_handle) dest
CROSS APPLY sys.dm_exec_query_plan(sdeq.plan_handle) deqp
where sdeq.is_next_candidate in (0,1)
order by is_next_candidate desc, queue_id, wait_order

Posted in SQL Server | Leave a Comment »