The following question was posed to me during a call today and its one that I’ve heard quite a bit so I figured it warranted a blog post.

The answer is, absolutely!  SELECT statements acquire a shared lock on the tables being accessed.  This shared lock will not affect other SELECT statements hitting the same table, but if someone tries to modify data in the table (via an UPDATE statement for example), the UPDATE statement will be blocked.  When analyzing locks using sp_lock the (S) symbol indicates a shared lock is on an object.

This default behavior can be modified by using one of many query hints.  Using the NOLOCK hint on a SELECT statement will force SQL Server to read data from the table without creating a shared lock on it.  When using this hint, you run the risk of reading uncommited data from the database but in cases where reading data with 100% accuracy is not required, NOLOCK can dramatically reduce blocking and improve the performance of your SELECT queries.  Another option is to use the READ UNCOMMITED isolation level when running your transactions, which conceptually does the same thing.

One additional caveat about both methods.  If your SELECT statement is running, and it expects to read a page that has been deleted by a transaction that is currently executing, SQL Server may deadlock this transaction.  There is an interresting post on this topic that can be found here.

I ran into an interresting situation today while working with a customer and thought it worthwhile to blog on the subject since my internet searching proved lengthy on the subject.  While trying to run a RECONFIGURE statement after making an sp_configure change I received the following message:

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

I’m running on SQL Server 2005 SP2+ so my first thought was… obviously, but I’m not trying to update system catalogs, I’m trying to make an sp_configure change.  After doing some digging I found that the culript was the sp_configure ‘allow updates’ parameter.  This configuration in SQL Server 2000 allowed or dissalowed direct system table updates.  In SQL Server 2005, this configuration item still exists but it is obsolete since direct access to system tables in always prohibited.  While the configuration item is obsolete, having it set to 1 in SQL Server 2005 requires you to run the RECONFIGURE statement using WITH OVERRIDE, otherwise you will get the message above.

If you ever see the message above when trying to run RECONFIGURE you will either need to run RECONFIGURE WITH OVERRIDE, or first run:

EXEC sp_configure ‘allow updates’, 0
RECONFIGURE

I find it confusing that an “obsolete” configuration parameter can have an effect on instance behavior, but oh well.  I’d love to hear comments if anyone has any insight into this or similar issues.

Thanks to Jasper Smith, who posted here and pointed me in the right direction.

I had a great time speaking with the large communities of SQL Server users as I traveled about Europe last week and much of this week.  I’m always impressed by the skill, intelligence, and creativity of these professionals.

And sometimes they raise questions that I think are worth sharing with everyone because they too might’ve heard the question before or asked it themselves.  Here are a few examples.  What are your thoughts on these questions?

1. To what extent does compatibility mode effect the behavior of SQL Server?  For example, direct catalog updates aren’t supported in SQL Server 2005. If we’re running in SQL Server 2000 compatibility-mode, can we still access the SYS schema?

2. There are strong architectural reasons why SQL Server doesn’t support multiple log readers, right?  Let’s discuss.  (I have my own ideas about why you wouldn’t want to enable it.)  However, the potential for improving the performance of replication by the use of multiple Log Reader agents is very strong because you could have more data flowing from one server to another.  Imagine for example, if you could have one log reader pumping data for the “customer” table and another pumping data for the “orders” table.  How can we build a solution that offers us those benefits?

3. Error messages?  Yes, error messages.  Is there any way we can find out what error messages are most commonly encountered across the SQL Server universe?  Maybe someone on the PSS team can help us out here?

4. How do I give a good presentation?  Well, there’s a hilarious YouTube video that covers all the bases - http://www.youtube.com/watch?v=cagxPlVqrtM&feature=related.  But if you’re looking for some real resources, here are some others that are useful:

Good web sites from Garr Reynolds and his associated  book:
http://www.presentationzen.com/
http://www.garrreynolds.com/Presentation/index.html
http://www.amazon.com/gp/product/0321525655/103-6148611-3957463?ie=UTF8&tag=garrreynoldsc-20&linkCode=xm2&camp=1789&creativeASIN=0321525655

Guy Kawasaki web site:
http://blog.guykawasaki.com/
http://blog.guykawasaki.com/2008/09/winners-of-worl.html
http://blog.guykawasaki.com/2005/12/the_102030_rule.html

How to Give a Steve Jobs Caliber Keynote Presentation:
http://www.maclife.com/article/how_to_give_a_steve_jobs_caliber_keynote_presentation?page=0%2C0

So what do you think about these questions?

Best regards,

-Kevin

Absolutely. You can use the sys.objects view to determine the create_date and modify_date for an object. The query would be:


select [name],[create_date],[modify_date]
from [sys].[objects]
where [type]=N’P’ and [is_ms_shipped]=0


To see the full definition of the procedure you would run:


select [name],[create_date],[modify_date],[definition]
from [sys].[objects] obj
join [sys].[sql_modules] mod
on obj.[object_id]=mod.[object_id]
where [type]=N’P’ and [is_ms_shipped]=0


Yes, this is bad because it can lead to your log shipped database getting out of sync and will probably result in Error 4305; error 4305 states that the log in a backup set is too late to apply, which means your log shipping jobs will fail until you can synchronize the databases. You should schedule your transaction log backup job to stop before your full backup job begins and then to restart once it has completed. See the Microsoft TechNet article on setting up Log Shipping.

When you perform a a full database backup SQL Server stores the ending log sequence number (LSN), which becomes the starting LSN for the next transaction log backup. So, if you are performing a transaction log backup while your full database backup is executing, and that transaction log backup is successful, once it is shipped and applied to the subscriber it will have a different starting LSN than the ending LSN of the full backup. This is not allowed and SQL Server will throw error 4305.

Q: I have inherited a legacy applications of which I am certain there are many unneccesary tables and indexes.  I am slightly paranoid about ripping them out however as I am not 100% sure that they are not used.  Is there any way that I can determine which indexes and tables are safe to drop?

A: As long as your application is running on SQL Server 2005+, you have access to a Dynamic Management View (DMV) that displays exactlky this information.  Querying from sys.dm_db_index_usage_stats will tell you how many times a given index/table has been scanned, seeked, looked up, updated, and even gives you the last date when an operation happened.  Browsing through this DMV will allow you to determine with confidence any index/table that has never been touched (since SQL Server was last restarted).  There are many exampes of some elegant queries that people have written that can be found by searching the DMV name but its a fairly straight forward one to understand.

One got-chya, the data in sys.dm_db_index_usage_stats will show tables/indexes across all databases where sys.indexes only shows you data from the database you are in.  This creates some challenging hurdles if you are trying to join on those tables to lookup the index name.  It’s best to keep it simple and filter dm_db_index_usage_stats to only show you data from the database you are in.

A fairly simple example with explanation can be found here.

Q:  We use DB2 Connect to access DB2 from MS Access. Do we need this for SQL Server also or does SQL Server have a built-in interface or do we need something else?

A: No you do not need to use DB2 Connect in able to access DB2 data within SQL Server.  SQL Server supports linked servers which allow you to register a remote data source in SQL Server and execute queries against it.  Linked servers can be remote SQL Server instances or instances of Oracle, DB2, Sybase, MySQL, etc…  As long as there is a supported OLEDB driver for the data source you can add it as a linked server.  There is a blog post you can read here:

http://blogs.msdn.com/dotnetinterop/archive/2006/01/20/defining-a-db2-as-a-linked-server.aspx

that walks you through the process of setting up a linked server to DB2 and you can also look up Linked Servers in books online for more information.

This is not an entirely new post, I won’t lie. Lately I’ve been asked about signal waits quite a few times, so I wanted to point to a past post. In Scheduler Yields = CPU Pressure…or do they? I discussed signal waits and talked a bit about wait event analysis. Here’s some additional and summary information… Wait event analysis is the practice of looking at SQL Server wait events to determine the cause of specific instance/database/statement/application behavior. Microsoft refers to this as analyzing waits and queues. Basically, the execution model for a session dictates that each session is either running, waiting or sleeping. The queues organize sessions into buckets of running, runnable or suspended. If a session is running, it is actively running and using CPU resources. If a session is runnable, it is waiting for CPU time. If a session is suspended, it is waiting for another physical resource to become available. This is best described using the [fantasy version of a] supermarket analogy where CPU is a cashier, a session is a person in line (in the queue to use the UK vernacular) and a resource is anything from change to preferred shopper cards to coupons:

• When a customer gets to the cashier, he/she is running (using CPU)
• Everyone else in line is runnable, waiting for time with the cashier
• As soon as the customer needs to reach for change, dig for coupons, he/she is immediately moved to the back of the line (put in the suspended queue) and the next customer in line (in the runnable queue) gets to move up to the cashier

Where people get confused is when signal waits come into play. Signal waits are actual waits for CPU time - pure CPU wait measurements. So, if you are analyzing wait events, and are trying to come up with an aggregated time for instance/database/statement/application activity, you need to add up the wait event time AND the signal wait time to get a complete picture. I’ll post more on this topic at a later time to go into more detail. If you have any questions or want more information on specific information, please comment.

I find that auto_update statistics in SQL Server is a really good thing.  Without it, many third-party applications would simply fall over from lack of preventative maintenance.  With it, they are able to run for extraordinarily long periods of time without really needing a full-time DBA to check up on the databases.

Having said all of that, auto_update statistics is a mystery for many of us.  This is reflected in the fact that I get lots of questions about auto_update statistics when I speak publicly (which seems to be at least once per month these days).  The most common question is “why isn’t auto_update statistics running?”

There are many elements to the answer to this question.  Naturally, you’re not going to have any new statistics in a database that is marked as readonly.  However, you might not know that approximately 20% of a table needs to change before triggering an auto_update statistics run for the given table.  If the table is really big, it’s possible that you’re simply not changing enough data.

Also remember that statistics are not the same in SQL Server 2005 as they are in SQL Server 2000 and that they must be upgraded using the sp_updatestats stored procedure after you migrate from version 2000 to 20005.  Otherwise, the old statistics are unusable by the query engine.  Once you’ve run sp_updatestats, the engine will maintain them automatically.  Be sure to check the article “After Upgrading the Database Engine” at http://msdn2.microsoft.com/en-s/library/ms143695.aspx to make sure you covered all the bases.

Thanks to Connor Cunningham and Simon Sabin (http://sqlblogcasts.com/blogs/simons) for help on this post.

First things first - depending on the version of SQL Server that you’re using, SQL Server could be creating this problem for itself. See these articles if you’re running SQL Server 2005 pre-SP2:

• SQL Server becomes sluggish or appears to stall on 64 bit installations
• A significant part of sql server process memory has been paged out. This may result in performance degradation
• How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
• The SQL Server Working Set Message

So what’s going on? First we need to address two concepts: Virtual Address Space and Working Set. Windows uses virtual address space (VAS) to allocate memory to applications (that’s as deep as I’m going here). A program’s working set is the collection of VAS pages that have been recently referenced. When a process is created, it is assigned a minimum and maximum working set size. As the working set size increases, thereby increasing memory demand, the virtual memory manager will attempt to keep up to the maximum working set size (memory) resident without exceeding the maximum threshold.

One common misconception is that there is as much virtual memory available to a process as there is physical memory (RAM) on a machine. Not so (or I wouldn’t be going there); you can get much more depth by reading Slava OKs’s blog on VAS. One very important point from a SQL Server perspective, however, is that when you’re configuring min and max server memory, you have to take into account the MemToLeave area. MemToLeave is VAS that’s left unused when SQL Server starts so that external components have address space to consume when they’re loaded. Typically, the majority of memory allocations from MemToLeave are from non-SQL Server memory consumers like COM objects, extended stored procedures, and linked servers. You can use DBCC MEMORYSTATUS to see these, but bear in mind that what’s reported will only reflect allocations made by SQL Server directly (SQL Server has no knowledge of outside memory requests).

So, when troubleshooting out of memory errors, do your homework.

• Check the list above to see whether your SQL Server is suffering from a known SQL Server bug, and understand (at least a little bit about) VAS and memory allocations.
• You can use Performance Monitor (Task Manager won’t help here) to get an idea of how much of VAS is currently consumed inside of your process by looking at the virtual bytes counter.
• If you believe that a 3rd party application is loading DLLs into SQL Server’s VAS, or you want to see if your own CLR or external procedures are doing so use SQL Server’s DMV sys.dm_os_loaded_modules to see what’s been loaded into SQL Server’s address space.

Oh, and by the way, for anyone who’s used to configuring the working set size parameter with sp_configure, you need to be aware that it’s been deactivated in SQL Server 2005.

Hope that helps.