The inside scoop on SQL / debugging you won't find anywhere else!
Mumbai Tech Community
SQL High Availability
SQL Server 2014
SQL Wait Types
Browse by Tags
Spread the word!
Tagged Content List
Query of the day: finding SQL Server queries with large memory grants
Quick tip from me today: I recently had to check on which T-SQL query / queries in the system were using up some monster 30GB+ query grants. Luckily the sys.dm_exec_query_memory_grants DMV facilitates this. Here is the query I finally used to figure out what was happening: SELECT r.session_id ...
6 May 2015
Exotic spinlocks: X_PACKET_LIST and XID_ARRAY
A Twitter conversation sparked this blog post; the question there was about the relevance of the X_PACKET_LIST spinlock. As with most spinlocks, there is no public documentation on this one, and that is primarily because the spinlock is an implementation detail and can change from release to release...
13 May 2014
Tracking TEMPDB internal object space usage in SQL 2012
It is a documented fact that using the MAX specifier can result in TEMPDB usage in specific cases. From the TEMPDB whitepaper , some of these can use ‘internal object’ space within TEMPDB: “all of the large object types: text , image , ntext , varchar(max) , varbinary(max) , and all others.” Now, a FAQ...
24 Feb 2014
Indexed views with remote tables–possible?
Once upon a time, I had this question come up during an onsite visit: someone wanted to create a ‘materialized’ copy of the remote data locally as an indexed view. I had never heard that requirement before, but since the motivation was genuine, I decided to validate it. There is a long list of what is...
28 Jan 2014
Exotic spinlocks: XTS_MGR
My friend and colleague - Fabricio Catae - had blogged about spinlocks previously. In one of the comments, a visitor has asked him about the XTS_MGR spinlock. There is no public documentation about this spinlock, because it is largely implementation specific detail. However, a general explanation...
22 Jan 2014
Normalizing T-SQL text, part 1: using the RML Utilities and the DMVs
A common problem when dealing with workloads which issue ad-hoc SQL commands (i.e. without parameterization) is to find out the ‘normalized’ version of the pattern. For example, these three statements are essentially the same ‘template’: SELECT BusinessEntityId FROM Person.Person WHERE LastName = ‘Smith...
30 Dec 2013
Debugging story: Slowness due to NTFS short file (8.3) name generation
When I teach production debugging to my customers, I always tell them that be successful you need to not only know the right tool and command syntax, but also know the right methodology . And perhaps even more importantly when debugging certain types of issues, knowledge of Windows Internals and the...
19 Jun 2013
Considerations when using the TransactSql.ScriptDOM parsers
Some of you might be aware of the above namespace , which holds an implementation of a first-class T-SQL parser. In this post I would like to explain some of the complexity you will face when dealing with the ScriptDOM yourselves, typically using Visitor pattern. Case Study Our objective in this case...
27 Mar 2013
SQL collation and performance
Recently a colleague asked me if SQL collations have any impact on performance. We also hit upon another question: do local variables (DECLARE @somevar <datatype>) have a collation associated with them and if so how is it controlled? Let us take some simple examples to demonstrate these points...
17 Sep 2012
Windows Performance Toolkit: Finding CPU consumer
A colleague of mine recently asked the question ‘I see CPU N is constantly pegged at 100%; is there any good way to determine which specific process is using that specific CPU?’ Well, he already figured that Task Manager or Process Explorer does not provide that information. And by definition purely...
7 Sep 2012
TEMENOS T24 Core Banking Optimized on Microsoft SQL Server Database Platform
I found these links today in response to a customer question around 'real world' benchmarks for financial applications running on SQL Server. These benchmarks describe the TEMENOS T24 Core Banking system, which is probably as 'real world' as it gets. http://blogs.technet.com/cfs-file.ashx/__key/CommunityServer...
2 Nov 2011
SQLDiag Configuration Tool released
My colleague and friend, boB Taylor (the spelling is not a typo – please visit http://www.opsvault.com/meet-our-contributors and see why) has recently released a GUI tool which allows you to configure the XML files required for SQLDiag to capture specific types of events, counters etc.) Download...
27 Apr 2011
OPTION(RECOMPILE) redux (a.k.a. Parameter Embedding Optimization not working)
A long time ago, I had blogged about the perils of ‘wildcard’ query patterns. As a response to one of the comments in that post, I learnt about a new optimization introduced in SQL 2008 wherein the OPTION (RECOMPILE) hint would help in these kind of cases. Ever since then, I had used this in some customers;...
15 Feb 2011
Performance Dashboard Reports in SQL Server 2008
While Activity Monitor in SQL 2008 does a good job depicting waiting tasks, top N queries etc.; many DBAs are simply too familiar with the erstwhile SQL Server 2005 Performance Dashboard Reports. While officially Microsoft does not support the usage of these reports with SQL 2008, I found two links of...
25 Jun 2010
Wait types in SQL Server
Troubleshooting performance issues in SQL Server involves studying the wait types and wait times associated with the queries executing. However, the relevance of each of the various wait types in the output can be a mystery. For SQL Server 2000, the cumulative wait types can be observed using the...
10 Jun 2006
Page 1 of 1 (15 items)
© 2015 Microsoft Corporation.
Privacy & Cookies