Wednesday, 15 February 2006

Getting information about referenced and referencing tables

At times we do need to carry out some tasks related to figure out certain information about references among some database objects. Today's post is going to show an example for tables by using T-SQL. There are two SQL Server system views we will use to query that information, they are sys.objects and sys.sysreferences.

The view sys.objects contains information for each database object (DDL and DML triggers) created inside of a particular user schema, and sys.sysreferences has the following important columns which will give us the object id of the referenced and referencing object.
  • rkeyid: Contains the ID of the object which is being referenced.
  • fkeyid: Contains the ID of the object which is referencing.  
For instance, now we are going to figure out which tables are being referenced from the 'Product' table inside the 'AdventureWorks' database:

SELECT S.[name] AS 'Referenced Table'
FROM sys.objects S INNER JOIN sys.sysreferences R 
   ON S.OBJECT_ID = R.rkeyid
WHERE S.[type] = 'U' AND R.fkeyid = OBJECT_ID('[Production].[Product]')

Having done that, we can also figure out which tables are referencing to the 'Product' table:

SELECT S.[name] AS 'Referencing Table'
FROM sys.objects S INNER JOIN sys.sysreferences R 
   ON S.OBJECT_ID = R.fkeyid
WHERE S.[type] = 'U' AND R.fkeyid = OBJECT_ID('[Production].[Product]')

As I said earlier, not only can we use those views for tables, but also for other objects like functions, stored procedures, views, etc. I hope you can make the most out of this tip. Thanks for reading.

Saturday, 4 February 2006

Improving the performance by using 'Boost SQL Server Priority' option

Boost SQL Server Priority is one of the many options that we can configure at SQL Server instance level in order to improve the performance of the database engine by changing its priority running on Microsoft Windows Server.

By default, the value of this option is 0 (zero) which says to Windows Server to execute SQL Server under normal priority either running on one processor or symmetric multiprocessor environment. On the other hand, if we configure this value to 1, SQL Server will be executed with a major priority which could improve the performance of a database server. This option works very well on servers which are only dedicated to SQL Server. It is not highly recommended using it when there are more applications running on the same server.

Boost SQL Server Priority option can be configured via either SQL Management Studio or T-SQL. In this post we are going to see how to configure it via the second way. Because this option is an Advanced Option, we firstly need to enable the possibility of accessing to it, which means that we have to enable 'show advanced options' option:
sys.sp_configure 'show advanced options', 1
Shortly after that, we must confirm the change without restating the database engine:
Having done that, we move on configuring 'Boost SQL Server Priority'.
EXEC sys.sp_configure N'priority boost', N'1'
Now we need to commit and override the change.
That is all we need to do in order to enable this option. It couldn't have been simpler.
To sum up, at this point I need to ask you to work with a lot of cautiousness as this configuration will impact on the whole database engine performance. If there were many SQL Server database engines running on the same server and this option was enabled only for some of them, the rest of them would be impacted negatively. Nevertheless, I personally do not recommend enabling it on machines running on only one processor or many database engines (or other applications). Thanks for reading.