Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This error message is due to the server setting of Remote Connection Query Timeout. The default is 600 seconds, or 10 minutes. [cc lang=”sql”] EXEC SP_CONFIGURE ‘remote query timeout’, 1800 reconfigure EXEC sp_configure EXEC SP_CONFIGURE ‘show advanced options’, 1 reconfigure EXEC sp_configure EXEC SP_CONFIGURE ‘remote query timeout’, 1800 reconfigure EXEC sp_configure [/cc] After making this change, make sure to close the window and create a new connection in order to inherit the new query timeout settings.
Continue reading ...
One new feature that SQL Server 2008 offers is more reliable dependency information. SQL Server 2005 offered a DMV (dynamic management view) called sys.sql_dependencies. It is now replaced by a more reliable sys.sql_expression_dependencies. The following script will show all the procedures that reference a given table name, along with the columns the procedure references also. Please note, this does not take into account any dynamic SQL. [cc lang=”sql”] USE MYDatabase GO DECLARE @TableName varchar(100) SET @TableName = ‘mytable’ SELECT SourceSchema = OBJECT_SCHEMA_NAME(sed.referencing_id) ,SourceObject = OBJECT_NAME(sed.referencing_id) ,ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME()) ,ReferencedSchema = ISNULL(sre.referenced_schema_name, OBJECT_SCHEMA_NAME(sed.referencing_id)) ,ReferencedObject = sre.referenced_entity_name ,ReferencedColumnID = sre.referenced_minor_id ,ReferencedColumn = sre.referenced_minor_name FROM sys.sql_expression_dependencies sed CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ‘.’ + OBJECT_NAME(sed.referencing_id), ‘OBJECT’) sre WHERE sed.referenced_entity_name = @TableName AND sre.referenced_entity_name = @TableName [/cc]
Continue reading ...
[cc lang=”sql”] DROP INDEX table_name.index_name [/cc]
Continue reading ...
One of the most common database performance issue most companies face is the improper choice of indexes or the lack of indexes. Most essential, is the clustered index. A well-chosen clustered index will save CPU, Memory, and IO. Choosing the clustered index for a table goes hand-in-hand with the writing of the queries for a table. It is best to design the queries and the index simultaneously to keep consistency. The majority of the time this is not an option. When we have not written the queries, the best way to understand the optimal clustered index is to study the queries that are run against the table. Once you understand the queries that run against a table, it is helpful to think of each of these queries broken down into three parts: The select list – Be on the lookout for the queries that return the most columns from the table. The join columns – The most common columns used to join other tables against the table. The where clause – The most common columns used to filter the result sets. Avoiding Fragmentation Before going into depth in choosing a clustered index we need to know what causes fragmentation. We ideally want to answer no to the following: Will the clustered value(s) be updated? Can new rows be inserted between already inserted clustered rows? The reason for answering no above is because we do not want fragmentation at the clustered level. The clustered index is like the foundation of a […]
Continue reading ...
This solution does not require a variable and it also allows you to do joins against other tables within one statement. The substring function removes the leading comma. Download the presidents table here. [cc lang=”sql”] SELECT ColumnList = SUBSTRING( (SELECT ‘,’ + ISNULL(President,”) FROM dbo.Presidents FOR XML PATH(”)) ,2,8000) [/cc] Using a common table expression (CTE) you can also recursively union the CTE with itself in order to generate a comma delimited list. This is a useful function for a CTE. The CTE itself does concatenate more than what is needed however it’s still relatively clean. Download the presidents table here. [cc lang=”sql”] ;WITH PresidentList (PresidentNumber, President) AS ( SELECT 1 , CAST(” AS VARCHAR(max)) UNION ALL SELECT cte.PresidentNumber + 1 ,cte.President + pl.President + ‘, ‘ FROM ( SELECT RowNum = Row_Number() OVER (ORDER BY PresidentNumber) ,President FROM dbo.Presidents ) pl JOIN PresidentList cte ON pl.RowNum = cte.PresidentNumber ) SELECT TOP 1 President FROM PresidentList ORDER BY PresidentNumber DESC [/cc] The next two examples require a variable, and when using a variable you cannot display any other columns from the table in the result set. There is a misnomer floating around much of the TSQL community with regards to concatenating strings using COALESCE. In actuality, this has nothing to do with COALESCE. COALESCE is simply used to replace a potential NULL value in one of the rows. Here is the code without using a COALESCE. [cc lang=”sql”] DECLARE @ColumnList varchar(8000) SET @ColumnList = ” — Set this to empty […]
Continue reading ...
