Database suddenly slow
-
One of our reporting database is becoming mysteriously slow in the last 2 weeks. While it was working perfectly fine before that. There was 2 tables with about 10 millions records each and it was working fine. Now I have 3 tables with about 10 millions records (dunno if it change anything) bu t I am starting to have plenty of Timeout exception! :O Case in point This is simplest EF call:
(
from ite in this.ItemTransactions
where ite.DataSourceId == 3
orderby ite.ItemTransactionId descending
select new { ite.PrimaryIdInDataSource }
).FirstOrDefault()and it generates this simple SQL
-- Region Parameters
DECLARE @p0 Int = 3
-- EndRegion
SELECT TOP (1) [t0].[PrimaryIdInDataSource]
FROM [ItemTransaction] AS [t0]
WHERE [t0].[DataSourceId] = @p0
ORDER BY [t0].[ItemTransactionId] DESCNow it takes 2 minutes to run that SQL code in SQLSMS, which is suspiciously slow for only 6 millions records. Also, when I run my C# code, EF timeout! Even thought I set the command timeout to be 10 minutes! :O Most puzzling all this code was running fine 2 weeks ago... Can't find the reason in the TFS history.. :~ -- [EDIT: Found it] Between 2 recreations and data repopulation of the database.. someone one changed the primary key of many tables that were used in joins from CLUSTERED to NONClUSTERED index That seemed like a good idea at the times, they said.... ;P
A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
-
One of our reporting database is becoming mysteriously slow in the last 2 weeks. While it was working perfectly fine before that. There was 2 tables with about 10 millions records each and it was working fine. Now I have 3 tables with about 10 millions records (dunno if it change anything) bu t I am starting to have plenty of Timeout exception! :O Case in point This is simplest EF call:
(
from ite in this.ItemTransactions
where ite.DataSourceId == 3
orderby ite.ItemTransactionId descending
select new { ite.PrimaryIdInDataSource }
).FirstOrDefault()and it generates this simple SQL
-- Region Parameters
DECLARE @p0 Int = 3
-- EndRegion
SELECT TOP (1) [t0].[PrimaryIdInDataSource]
FROM [ItemTransaction] AS [t0]
WHERE [t0].[DataSourceId] = @p0
ORDER BY [t0].[ItemTransactionId] DESCNow it takes 2 minutes to run that SQL code in SQLSMS, which is suspiciously slow for only 6 millions records. Also, when I run my C# code, EF timeout! Even thought I set the command timeout to be 10 minutes! :O Most puzzling all this code was running fine 2 weeks ago... Can't find the reason in the TFS history.. :~ -- [EDIT: Found it] Between 2 recreations and data repopulation of the database.. someone one changed the primary key of many tables that were used in joins from CLUSTERED to NONClUSTERED index That seemed like a good idea at the times, they said.... ;P
A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
There are umpteen reasons why a database may start having performance issues - it is unlikely to be your unchanged code! This post lists some of the troubleshooting steps you can try to either find out what is wrong and/or just do some housekeeping that should help [SOLVED] SQL Server database slowness troubleshooting - Spiceworks[^]
-
One of our reporting database is becoming mysteriously slow in the last 2 weeks. While it was working perfectly fine before that. There was 2 tables with about 10 millions records each and it was working fine. Now I have 3 tables with about 10 millions records (dunno if it change anything) bu t I am starting to have plenty of Timeout exception! :O Case in point This is simplest EF call:
(
from ite in this.ItemTransactions
where ite.DataSourceId == 3
orderby ite.ItemTransactionId descending
select new { ite.PrimaryIdInDataSource }
).FirstOrDefault()and it generates this simple SQL
-- Region Parameters
DECLARE @p0 Int = 3
-- EndRegion
SELECT TOP (1) [t0].[PrimaryIdInDataSource]
FROM [ItemTransaction] AS [t0]
WHERE [t0].[DataSourceId] = @p0
ORDER BY [t0].[ItemTransactionId] DESCNow it takes 2 minutes to run that SQL code in SQLSMS, which is suspiciously slow for only 6 millions records. Also, when I run my C# code, EF timeout! Even thought I set the command timeout to be 10 minutes! :O Most puzzling all this code was running fine 2 weeks ago... Can't find the reason in the TFS history.. :~ -- [EDIT: Found it] Between 2 recreations and data repopulation of the database.. someone one changed the primary key of many tables that were used in joins from CLUSTERED to NONClUSTERED index That seemed like a good idea at the times, they said.... ;P
A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
Check the execution plan. Check that you have the correct indexes on the tables. Try running Brent Ozar's First Responder Kit[^] on the server to see if there are any obvious errors. You mention that the size of the data has increased. Does it now exceed the server's available memory? If it keeps having to go back to disk to load the data, then that can dramatically slow things down. Especially if the data isn't on an SSD.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
There are umpteen reasons why a database may start having performance issues - it is unlikely to be your unchanged code! This post lists some of the troubleshooting steps you can try to either find out what is wrong and/or just do some housekeeping that should help [SOLVED] SQL Server database slowness troubleshooting - Spiceworks[^]
Found it... Between 2 recreations and data repopulation.. someone one changed the primary key of many tables that were used in joins from CLUSTERED to NONClUSTERED index That seemed like a good idea at the times, they said.... ;P
A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
-
Check the execution plan. Check that you have the correct indexes on the tables. Try running Brent Ozar's First Responder Kit[^] on the server to see if there are any obvious errors. You mention that the size of the data has increased. Does it now exceed the server's available memory? If it keeps having to go back to disk to load the data, then that can dramatically slow things down. Especially if the data isn't on an SSD.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Found it... Between 2 recreations and data repopulation.. someone one changed the primary key of many tables that were used in joins from CLUSTERED to NONClUSTERED index That seemed like a good idea at the times, they said.... ;P
A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
-
Found it... Between 2 recreations and data repopulation.. someone one changed the primary key of many tables that were used in joins from CLUSTERED to NONClUSTERED index That seemed like a good idea at the times, they said.... ;P
A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
:laugh: :laugh: :laugh: would you like to borrow my clue bat.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
One of our reporting database is becoming mysteriously slow in the last 2 weeks. While it was working perfectly fine before that. There was 2 tables with about 10 millions records each and it was working fine. Now I have 3 tables with about 10 millions records (dunno if it change anything) bu t I am starting to have plenty of Timeout exception! :O Case in point This is simplest EF call:
(
from ite in this.ItemTransactions
where ite.DataSourceId == 3
orderby ite.ItemTransactionId descending
select new { ite.PrimaryIdInDataSource }
).FirstOrDefault()and it generates this simple SQL
-- Region Parameters
DECLARE @p0 Int = 3
-- EndRegion
SELECT TOP (1) [t0].[PrimaryIdInDataSource]
FROM [ItemTransaction] AS [t0]
WHERE [t0].[DataSourceId] = @p0
ORDER BY [t0].[ItemTransactionId] DESCNow it takes 2 minutes to run that SQL code in SQLSMS, which is suspiciously slow for only 6 millions records. Also, when I run my C# code, EF timeout! Even thought I set the command timeout to be 10 minutes! :O Most puzzling all this code was running fine 2 weeks ago... Can't find the reason in the TFS history.. :~ -- [EDIT: Found it] Between 2 recreations and data repopulation of the database.. someone one changed the primary key of many tables that were used in joins from CLUSTERED to NONClUSTERED index That seemed like a good idea at the times, they said.... ;P
A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
Check your index if the fragmentation is high then re organize it or re index. And If you have index like non cluster or cluster and you changed it or add new index please review.thanks
