Report on Indexes on FKs

This question had been asked in NG yesterday.I think it is a good exercise so I take a script too get the column usage from both sides (parent/child) written by Aaron Bertrand and modified a little bit to return the table/column that does not have an index on.

WITH fk_no_indexes
AS
(
SELECT
[constraint_name] = f.[name],
[child_table] = OBJECT_NAME(f.parent_object_id),
[child_table_id]=f.parent_object_id,
[child_column] = cc.name,
[child_column_id]=cc.[column_id],
[parent_table] = OBJECT_NAME(f.referenced_object_id),
[parent_column] = pc.name
FROM
sys.foreign_keys f
INNER JOIN
(
SELECTc.[object_id],c.name,c.column_id,ic.index_id
FROM sys.columns c INNER JOIN sys.index_columns ic
ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
) AS pc
ON
f.key_index_id = pc.index_id
INNER JOIN sys.foreign_key_columns fkc
ON f.[object_id] = fkc.constraint_object_id
AND pc.[object_id] = fkc.referenced_object_id
AND fkc.referenced_column_id = pc.column_id
INNER JOIN sys.columns cc
ON fkc.parent_object_id = cc.[object_id]
AND fkc.parent_column_id = cc.column_id
)
SELECT [constraint_name],[child_table],[child_column],[parent_table] FROM
fk_no_indexes WHERE NOT EXISTS
(
SELECT * FROM sys.index_columns i
WHERE i.[object_id]=[child_table_id]
AND [child_column_id]=column_id
)
ORDER BY
constraint_name,
child_table;

Have you applied latest sevice pack to the client tools?

Well, I have seen a huge number of developers who have not heard aboit it at all. Yes,I'm serious.Recently, I had helped out tuning SQL Server performance of the database in pretty big and to be held in respect company with more than 10 developers. One day one of them asked me about the query ,which restuns the data without the error on his machine ,however , throws the error on the server.If I remember well it was conversion error.I checked both queries and indeed on the dev.machine it runs without the error. I know there is no magic here..and as you can imagine the dev.machine has installed NO service pack at all,but Production SQL Server 2000 has latest service pack (SP4).That's all story. The 'little' difference could have made a big error witout testing as each developer has installed server and client tools on their machines.The SQL Server query optimizer is free to move/change expressions with new release of service packs or hotfix thus please make sure that you have latest service pack on your client tools and test the query as well.

How to upload/modify more than one BLOB column in single statement

Recently, I have been visited our client who has one table with two columns defined as VARBINARY(MAX)to store the images. How can we insert/upade these columns with single statements? I have not used blobs too much ,so I ended up with the following

CREATE TABLE Blobs (id INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,b1 VARBINARY(MAX),b2 VARBINARY(MAX))

---Single INSERT statement

INSERT INTO Blobs (b1,b2)
(SELECT *,(SELECT * FROM
Openrowset(Bulk 'N:\OnePicture.JPG', SINGLE_BLOB) AS d1)
FROM
Openrowset(Bulk 'N:\AnotherPicture.JPG', SINGLE_BLOB) AS d2)

--Update

UPDATE Blobs SET b1=(SELECT * FROM
Openrowset(Bulk 'N:\AnotherPicture.JPG', SINGLE_BLOB) AS d2),
b2=(SELECT * FROM
Openrowset(Bulk 'N:\OnePicture.JPG', SINGLE_BLOB) AS d2)
---WHERE condition put here

My wife gave birth to our third child on 4 May!

A son, 3 kg 370 grams!

We have replaced ourselves and are now done.

How to eat ASYNC_NETWORK_IO ?

Last time I was visiting our client the person showed me one process which was in suspended status for a long time. Using Adam's great script(http://sqlblog.com/blogs/adam_machanic/archive/2009/03/30/who-is-active-v8-40-now-with-delta-power.aspx) I identified the SELECT statement which had ASYNC_NETWORK_IO wait type. MS said that this type occurs on network writes when the task is blocked behind the network and we need to verify that the client is processing data from SQL Server. The problem was that SELECT statement retrievs more than 50.000 rows , then the application manipulates (FOR ..NEXT loop) with that data and finally only 200 rows that a client had seen at the end. Well,thanks to SQL Server 2005 we changed the statement to accept a parameter for TOP clause and this wait type has gone. We have not seen a great improvment of the application as we did not expect it but it is another way to identify poor written queries.

MVP Award for 2009 year.

I've been given the MVP award for another year. I hope you have found this blog and my posts in public forums useful and that helped you out to resolve some problems.

What is a shared drive/s used by SQL Server instance?

SQL Server 2005 has a great dynamic management view named sys.dm_io_cluster_shared_drives . If you have clustering and want to know what
shared disk/s which is/are used by clustered SQL Server instance run the following query.

SELECT DriveName
FROM sys.dm_io_cluster_shared_drives
ORDER BY DriveName