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
Exotic wait types: PREEMPTIVE_OS_ENCRYPTMESSAGE
Some time ago, we were discussing wait types with a group of customers. To demonstrate the waits-and-queues methodology we use, I showed the group the sys.dm_os_wait_stats DMV, and the results were sorted by wait_time_ms in descending order. On the top of that list was the PREEMPTIVE_OS_ENCRYPTMESSAGE...
21 Jan 2015
Too many files causing RAM pressure? (a.k.a. DynCache to the rescue!)
Readers of this blog might recall a previous post which described one impact of having too many similarly named files in one folder in NTFS. It turns out that the 8.3 naming convention is not the only thing you need to worry about when you have very large amounts of (smaller) files in the same volume...
20 Mar 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
SQL Server and Storage / SAN replication (mirroring) tips
Some customers have adopted block level replication of the storage disks (LUNs) as their DR solution for SQL Server databases. Remote mirroring of storage disks sounds great – transparent to the application, replicates every block as-is; so very little reconfiguration required on the other side, scales...
24 Apr 2013
Getting worker thread IDs using DMVs
In SQL 2000, the kpid column in sysprocesses was a convenient way to find out the actual worker thread ID (OS level thread identifier) for a given task. How does one do this using the DMVs? Here is a quick way: select R.Session_Id, Th.os_thread_id from sys.dm_exec_requests R join sys.dm_exec_sessions...
22 Mar 2013
Priority boost details – and why it’s not recommended
Some times, we see customer has (accidentally or otherwise) enabled the option ‘boost priority’ for SQL Server worker threads. In general Microsoft does not recommend that you set this option. Why? First a bit of background. When we set the ‘priority boost’ option using sp_configure what is happening...
26 Jan 2010
Some notes on database snapshots
Overview I recently conducted some tests to double check the exact behavior of database snapshots when: Snapshots are created on volumes which are much smaller than the actual database size Metadata-only operations such as DROP TABLE are executed DDL operations execute on the main database and the snapshot...
31 Dec 2008
T-SQL Anti-pattern of the day: 'all-in-one' queries
Scenario A common requirement for enquiry queries on an OLTP database is to have search criteria which are very specific ('get me details for for OrderID = NNNN') and also the occasional reports which ask for all the orders ('get me all the orders, no questions asked'.) Here is a sample from AdventureWorks...
19 Dec 2008
Resources from TechEd sessions
Several attendees have asked for the resources section from our presentations at TechEd India. Enjoy! Blogs SQLCAT: http://blogs.msdn.com/sqlcat WINCAT: http://blogs.msdn.com/wincat Slava Oks’ blog: http://blogs.msdn.com/slavao Product Feedback http://lab.msdn.microsoft.com/productfeedback/ ...
14 Jun 2006
DB Snapshots are a very useful new feature in SQL Server 2005 Enterprise Edition. They provide us the ability to preserve a consistent picture of the database as it was at a point in time. Both metadata and data are captured in the snapshot. The main uses of snapshots are for preserving views of...
7 Jun 2006
Page 1 of 1 (11 items)
© 2014 Microsoft Corporation.
Privacy & Cookies