August 29, 2016 at 2:58 am
Hi All,
I have a table which has million rows and database is set under RCSI. The table has a clustered primary key on Column1 and a non-clustered index on (Column2, Column3).
Now when I am deleting (using join with temp table) the data based on Column3 and if select statement runs on same table by other session then it is taking too much time. However it doesn't show any blocking as in RCSI: reader doesn't block writer & vice-versa.
When no delete statement is running on the table then the select statement is getting completed with a second.
Can anyone please put some light on why the select the taking time due to this delete statement??
August 29, 2016 at 5:38 am
Have you proved that no blocking is taking place? RCSI does some locking/blocking depending on the usage scenario.
You might like to query the sys.dm_tran_locks table when testing to see what happens when the DELETE statement runs. Also, check for foreign key constraints.
Maybe you could post the Actual Query Plans here and somebody will help.
August 29, 2016 at 7:31 am
I executed sp_whoisactive during the execution and there was no blocking at that time. We don't have FK at our database.
August 29, 2016 at 8:06 am
Did you query sys.dm_os_waiting_tasks during the delete?
Sue
August 29, 2016 at 8:50 am
Check for tempdb IO stalls. RCSI will copy data to tempdb. I have seen this multiple times at clients, sometimes to the point where they had to disable the feature until they could get tempdb performance improved significantly.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 30, 2016 at 1:13 am
The read latency is 32 ms & write latency is 95 ms for tempdb data file & for tempdb log file it is 8 for read & 45 for write latency. I tried to find out the baseline calculation to get the good or bad read/write latency but the generic answer is “it depends”. Somewhere they mentioned latency between 40ms -100ms as Bad.
We have only one tempdb file as we are using Amazon RDS where it doesn't allow us to add new data file to the tempdb.
Please suggest what steps we can take to reduce the latency.
August 30, 2016 at 8:43 am
IIRC AWS allows you to have multiple "disks" working together to improve IO performance. You can also move to SSD-based storage and also get dedicated IO.
You are just another example of companies putting there stuff up into the cloud without understanding a) just how poor "default" and low-tier performance really is and b) knowing what to do about it. If I had a nickel for every client and forum poster I have come across who has been blindsided by those things I could buy a small island - like maybe Hawaii. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 30, 2016 at 9:25 pm
Thanks Kevin.
Can you please tell me how to have multiple disks in AWS RDS. We are using SSD based storage. AFAIK in RDS we don't provide access to disks & can't add multiple data files to the system databases like tempdb. However we can add multiple files to the user database.
August 31, 2016 at 9:55 am
Tarun Jaggi (8/29/2016)
Hi All,I have a table which has million rows and database is set under RCSI. The table has a clustered primary key on Column1 and a non-clustered index on (Column2, Column3).
Now when I am deleting (using join with temp table) the data based on Column3 ...
If you are joining to the main table only by column3, and not also by column1 and/or column 2, then you need to create another nonclus index on the main table on column3. If the temp table can be large, cluster the temp table on column3.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 31, 2016 at 10:31 am
ScottPletcher (8/31/2016)
Tarun Jaggi (8/29/2016)
Hi All,I have a table which has million rows and database is set under RCSI. The table has a clustered primary key on Column1 and a non-clustered index on (Column2, Column3).
Now when I am deleting (using join with temp table) the data based on Column3 ...
If you are joining to the main table only by column3, and not also by column1 and/or column 2, then you need to create another nonclus index on the main table on column3. If the temp table can be large, cluster the temp table on column3.
Careful there. I can't count the number of times I have REMOVED index(es) from client code to provide a performance INCREASE to the total operation. The cost of maintaining or creating an index just to hit the object once is VERY seldom a win.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 31, 2016 at 10:33 am
Tarun Jaggi (8/30/2016)
Thanks Kevin.Can you please tell me how to have multiple disks in AWS RDS. We are using SSD based storage. AFAIK in RDS we don't provide access to disks & can't add multiple data files to the system databases like tempdb. However we can add multiple files to the user database.
Sorry, RDS, not VMs in Amazon. Missed that.
With RDS, like Azure, you are limited to what you can do with various configurations.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 31, 2016 at 11:49 am
TheSQLGuru (8/31/2016)
ScottPletcher (8/31/2016)
Tarun Jaggi (8/29/2016)
Hi All,I have a table which has million rows and database is set under RCSI. The table has a clustered primary key on Column1 and a non-clustered index on (Column2, Column3).
Now when I am deleting (using join with temp table) the data based on Column3 ...
If you are joining to the main table only by column3, and not also by column1 and/or column 2, then you need to create another nonclus index on the main table on column3. If the temp table can be large, cluster the temp table on column3.
Careful there. I can't count the number of times I have REMOVED index(es) from client code to provide a performance INCREASE to the total operation. The cost of maintaining or creating an index just to hit the object once is VERY seldom a win.
If you don't, SQL will be forced to do a full table scan. Particularly with RCSI in effect, that can lead to many deadlocks. I've removed thousands of indexes here, but when one is needed, it is needed. Here I believe that it is.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 31, 2016 at 12:00 pm
ScottPletcher (8/31/2016)
TheSQLGuru (8/31/2016)
ScottPletcher (8/31/2016)
Tarun Jaggi (8/29/2016)
Hi All,I have a table which has million rows and database is set under RCSI. The table has a clustered primary key on Column1 and a non-clustered index on (Column2, Column3).
Now when I am deleting (using join with temp table) the data based on Column3 ...
If you are joining to the main table only by column3, and not also by column1 and/or column 2, then you need to create another nonclus index on the main table on column3. If the temp table can be large, cluster the temp table on column3.
Careful there. I can't count the number of times I have REMOVED index(es) from client code to provide a performance INCREASE to the total operation. The cost of maintaining or creating an index just to hit the object once is VERY seldom a win.
If you don't, SQL will be forced to do a full table scan. Particularly with RCSI in effect, that can lead to many deadlocks. I've removed thousands of indexes here, but when one is needed, it is needed. Here I believe that it is.
But you are robbing Peter to pay Paul, right? Don't you have to do a full table scan to build the index on the temp table? And do the tempdb-hitting sort and then tempdb hit to write out the index data (or often worse populate the data with the clustered index in place already)??
And with a straight up join of large data it is almost guaranteed that the entire object will be hit - meaning a clustered index (table) scan. So why is that scan different from a heap scan?
Also, I may have missed it but I didn't see any mention by the OP of deadlocks occurring during this process.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 31, 2016 at 12:05 pm
TheSQLGuru (8/31/2016)
ScottPletcher (8/31/2016)
TheSQLGuru (8/31/2016)
ScottPletcher (8/31/2016)
Tarun Jaggi (8/29/2016)
Hi All,I have a table which has million rows and database is set under RCSI. The table has a clustered primary key on Column1 and a non-clustered index on (Column2, Column3).
Now when I am deleting (using join with temp table) the data based on Column3 ...
If you are joining to the main table only by column3, and not also by column1 and/or column 2, then you need to create another nonclus index on the main table on column3. If the temp table can be large, cluster the temp table on column3.
Careful there. I can't count the number of times I have REMOVED index(es) from client code to provide a performance INCREASE to the total operation. The cost of maintaining or creating an index just to hit the object once is VERY seldom a win.
If you don't, SQL will be forced to do a full table scan. Particularly with RCSI in effect, that can lead to many deadlocks. I've removed thousands of indexes here, but when one is needed, it is needed. Here I believe that it is.
But you are robbing Peter to pay Paul, right? Don't you have to do a full table scan to build the index on the temp table? And do the tempdb-hitting sort and then tempdb hit to write out the index data (or often worse populate the data with the clustered index in place already)??
And with a straight up join of large data it is almost guaranteed that the entire object will be hit - meaning a clustered index (table) scan. So why is that scan different from a heap scan?
Also, I may have missed it but I didn't see any mention by the OP of deadlocks occurring during this process.
Thought you were objecting to the index on the main table.
As to the temp table, definitely create the clus index prior to loading the table. Otherwise, yes, you do have to scan the data again. The goal is to get a merge join between the nonclus index on the main table and the clus index on the temp table. If you don't index the temp table, you'll be forced into a loop join (most likely) or a hash join if the temp table is really large, either of which will have much worse performance than a merge join.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 31, 2016 at 1:04 pm
ScottPletcher (8/31/2016)
TheSQLGuru (8/31/2016)
ScottPletcher (8/31/2016)
TheSQLGuru (8/31/2016)
ScottPletcher (8/31/2016)
Tarun Jaggi (8/29/2016)
Hi All,I have a table which has million rows and database is set under RCSI. The table has a clustered primary key on Column1 and a non-clustered index on (Column2, Column3).
Now when I am deleting (using join with temp table) the data based on Column3 ...
If you are joining to the main table only by column3, and not also by column1 and/or column 2, then you need to create another nonclus index on the main table on column3. If the temp table can be large, cluster the temp table on column3.
Careful there. I can't count the number of times I have REMOVED index(es) from client code to provide a performance INCREASE to the total operation. The cost of maintaining or creating an index just to hit the object once is VERY seldom a win.
If you don't, SQL will be forced to do a full table scan. Particularly with RCSI in effect, that can lead to many deadlocks. I've removed thousands of indexes here, but when one is needed, it is needed. Here I believe that it is.
But you are robbing Peter to pay Paul, right? Don't you have to do a full table scan to build the index on the temp table? And do the tempdb-hitting sort and then tempdb hit to write out the index data (or often worse populate the data with the clustered index in place already)??
And with a straight up join of large data it is almost guaranteed that the entire object will be hit - meaning a clustered index (table) scan. So why is that scan different from a heap scan?
Also, I may have missed it but I didn't see any mention by the OP of deadlocks occurring during this process.
Thought you were objecting to the index on the main table.
As to the temp table, definitely create the clus index prior to loading the table. Otherwise, yes, you do have to scan the data again. The goal is to get a merge join between the nonclus index on the main table and the clus index on the temp table. If you don't index the temp table, you'll be forced into a loop join (most likely) or a hash join if the temp table is really large, either of which will have much worse performance than a merge join.
Getting the merge join on a very large base table is the primary reason I didn't say "always" in my statement against indexes on temp objects.
As to the temp table, definitely create the clus index prior to loading the table. Otherwise, yes, you do have to scan the data again.
I disagree there certainly for those cases where the clustered index on the temp object isn't always increasing, and maybe not even then (identity excepted, but that is another almost always silly thing I see clients do with their temp tables). I put down a TODO item for myself to validate this belief with some testing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply