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.
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) begin print ''Database Name: '' + ''?'' select * from ?.sys.objects where type = ''U'' and name like ''%log%%'' and is_ms_shipped = 0 end'
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.
I am not sure how many of us pay attention to BOL notes under certain topics. I must admit that I have overlooked quite a few useful notes once in a while. But while working on a Linked Server permissions issue, I came across this point under the “Security for Linked Servers”:
To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.
The reason for this is that the query optimizer is dependant on the table/index statistics to decide on the most optimal plan for the query. For remote queries, that perform a large number of joins or have complex sub-queries or complex filter conditions, you are bound to run into permission issues if the linked server remote login doesn’t have the necessary permissions to extract the statistics information. If you are not willing to give the linked server login the above rights on the remote table, then it would be better to either use an alternative means to fetch the remote data or maintain a local copy of the data using mirroring/log shipping/replication and operate on the local data to make sure that your business logic doesn’t get affected as the data increases on your server.
IMHO linked server queries should never be used for implementing complex business logic! Remote queries should be used to fetch as minimal data as possible. But Utopia is not always a reality!
TroubleshootingSQL 