I recently worked on a soft-NUMA configuration issue for SQL Server 2008. I decided to document the steps to configure soft-NUMA in a blog post on SQLServerFAQ on MSDN. Here it is:
How to set Soft-NUMA for SQL Server 2008 R2
Happy NUMA configuration! 🙂 It can be quite daunting if you have multiple CPUs.
Understanding SQL version numbers can be a bit confusing at times and remembering the builds an even bigger hassle. I just replied with the following post on MSDN recently. I thought I would convert the post into a blog post for other people to see and use as appropriate.
SQL Server has the following components:
1. Database engine (Engine, Replication, Full-text)
2. SSIS – Integration Services (SQL Server 2005 and above)
3. Reporting Services
4. Tools and Shared Components (Eg. SSMS)
5. Notification Services (SQL Server 2005 and above)
6. Analysis Services
When you install a SQL Server component, the RTM version of that component is installed. The SQL Server release version has a version major number which will stay constant for a release. The 4 digit number that keeps varying is the build number of the current version of your component. This number changes whenever you apply a hotfix/security fix/cumulative update/service pack for that particular component of SQL Server. Very rarely do the build numbers remain constant across patches (Seen to happen in Reporting Services for certain builds).
Version major numbers: This is the first number in the version output:
SQL Server 7.0 – 7.00.XXXX
SQL Server 2000 – 8.00.XXXX
SQL Server 2005 – 9.00.XXXX
SQL Server 2008 – 10.00.XXXX
SQL Server 2008 R2- 10.50.XXXX
The above article has all the build numbers that were released for SQL Server.
If you happen to use Database Mirroring on SQL Server 2005, then Cumulative Update 10 for SQL Server 2005 Service Pack 3 is a must for your instances. There are 2 database mirroring related fixes in this cumulative package. I would suggest that all SQL Server 2005 instances that have database mirroring enabled need to have this update installed ASAP.
982933 (http://support.microsoft.com/kb/982933/ )
Error message when you shrink data files on principal in a Database Mirroring for two SQL Server 2005 servers
983480 (http://support.microsoft.com/kb/983480/ )
FIX: Various issues occur when a database is involved in a database mirroring session and in a transactional replication if trace flag 1448 is enabled
983500 (http://support.microsoft.com/kb/983500/ )
FIX: The role switch is delayed when a mirroring automatic failover occurs in SQL Server 2005
I have an application which has a bunch of specific permissions granted to some objects within the database. I encountered a problem while trying to migrate this application from one server to another because I needed to recreate these permissions which were added over the period of a year as and when new features were added to the application.
I ended up using sp_helprotect to list out the permissions for the objects that I was interested in. But I still needed to convert the permissions into the necessary GRANT/REVOKE statements. This can be tedious when you are trying to manipulate permissions across databases and multiple objects. What I ended up doing was writing a crude T-SQL script to help me create the necessary GRANT/REVOKE scripts based on the sp_helprotect output.
Note: sp_helprotect does not return information about securables that were introduced in SQL Server 2005. Use sys.database_permissions and fn_builtin_permissions instead.
So, if you have a securable that you are interested in which was introduced in SQL Server 2005, then this is not going to help you.
Note: This script has been tested only for the following objects:
2. Stored Procedures
3. Functions (UDFs/TVFs)
Modification: Aug 8, 2012: Thanks to Graham [blog] for suggesting the corrections.
set nocount on
-- Declare and initialize local variables
declare @newuser varchar(255)
set @newuser = 'newuser' -- This is the new user that you need to add permissions for
-- Create temp table to store sp_helprotect output
create table #tbl_permissions
-- Store sp_helprotect output in a temp table
insert into #tbl_permissions
exec sp_helprotect null,'olduser'
-- Get the necessary GRANT/REVOKE/DENY T-SQL commands
select RTRIM(LTRIM(ProtectType)) + ' ' +
CASE Action WHEN 'CONNECT' THEN Action + ' TO ['+@newuser+']'
ELSE Action + ' ON OBJECT::'+OWNER+'.'+OBJECT+' TO [' +@newuser+']'
-- Drop the temporary table
drop table #tbl_permissions
Now that a bit of my work was easier, I decided to put this script out on the web so that someone else can benefit from the same. I am sure that there are multiple T-SQL scripts which do the same but I found the one below very helpful. I shall try and make it more comprehensive by using sys.database_permissions in a future blog post.
If you have difficulty viewing this session, click here to view it in the original website.
Here is the recording of the webcast that I presented today! Feel free to contact me through this blog or post on the discussion forum on the SQL Nexus discussion forum.
In case you are not able to view the presentation, then you can view it directly on the Zeollar site.
This has been an age long debate on whether to use GUIDs or INTs. Thought I would pen down some points based on some tests that I ran on my test machine.
GUID vs INT Debate
SQL Backup softwares can cause Out Of Memory conditions if the BufferCount and MaxTransferSize is not taken into account on 32-bit systems:
Incorrect BufferCount data transfer option can lead to OOM condition
In the recent past, I worked on a few issues where the use of monotonically increasing clustered index keys were turning into hotspots in the database leading to latch blocking and causing performance degradation as an end result. I illustrate this with the help of an example in the blog post below.
Monotonically increasing clustered index keys can cause LATCH contention
Once in a while I find that need to find out if a particular table name exists in any of the databases of my SQL Server instance. This is when sp_MSforeachdb comes into play.
EXEC sp_MSforeachdb '
if exists(select * from ?.sys.objects where type = ''U'' and name like ''%log%%'' and is_ms_shipped = 0)
print ''Database Name: '' + ''?''
select * from ?.sys.objects where type = ''U'' and name like ''%log%%'' and is_ms_shipped = 0
The above piece of T-SQL code finds out if there exists a user table which has a string “log” in it’s name on all the databases of the instance. You can keep modifying this query by adding more filters in the WHERE clause. Current filters only looks for tables which are not shipped by Microsoft (Eg. user tables created by replication, database tuning advisor, log shipping etc.). Basically any user table created by you. If you want to use this on a SQL Server 2000 instance, then you would need to use sysobjects instead of sys.objects.