Slow Database

  • We made following 2 changes to our database which is being used by multiple sites:

    1. We converted columns from varchar to nvarchar in all tables of our database

    2. also, during this change, we had also added below index on Stories table which is having around 1,00,000 records:

    CREATE NONCLUSTERED INDEX IX_Stories

    ON Stories (WID, SID, IsActive, SDate, EDate)

    Since above changes are made, I think our sites are running too slow. We maintain server errors in "ErrorLog" table which is getting few thousand error records inserted from past few days after the change, which has following error descriptions:

    1. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    2. The wait operation timed out

    Can above database changes cause such issue ? We are unable to trace the exact reason and hence unable to fix it.

    I tried to run below command on database, but issue is same:

    exec sp_updatestats

    Our hosting provider is saying that we have a single database on the server which is being accessed my multiple websites. These 40 websites are having same connection string which uses sa user to connect to this same database. Our hosting provider said that we should create separate database user for each site. Can this be the reason for sites running slow and how can we confirm the same?

  • Yes, it is always better to have seperate database which will result into easy maintenance of the database and easy to narrow down the issue you will have on specific database.

    The issue required lot many details to investigate further.... Before schema change everything was working fine or sometime facing such issue of timeout? What is the size of the database? What error log says? If required use profiler to narrow down the issue. Issue is specific to one single website? List would go on unless you provide more details to investigate further.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • I would only suggest splitting up the databases if you have separate functionality that you're supporting. If the 40 web sites all have the same functionality, then breaking up your database into 40 copies could bring a whole slew of other headaches as you try to synchronize data across them.

    At this point, we don't know why things are running slow. You've described two changes that you made, varchar to nvarchar, and you added an index. You described one thing you tried to do to "fix" the perceived problem, sp_updatestats. What you haven't described is the status of your systems. What are your top 10 wait statistics, both by duration and frequency. That information will tell you where the system, as a whole is running slowly. You're getting timeouts, but do you know on which queries or procedures those timeouts are occurring on? Do you have query metrics for the top 10 most frequently called queries and the top 10 longest running queries? For those queries that are causing you pain, what do their execution plans look like?

    The first step to troubleshooting is not to take random pot shots at a solution, "maybe it's the statistics." The first step is to understand where the problem is and what's causing it. You don't know this. Go and get those metrics, then you can begin deciding what you have to do and how you'll need to do it.

    Yes, you may decided to break up your database, but don't do that until you know exactly why you're having issues. You may need better maintenance of your statistics or possible to defragment your indexes, but don't do these things until you have an understanding of what your issues are. You may need more or different indexes on your tables, but again, don't make changes until you've gather metrics so that you can do a before and after comparison.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • shirish_padekar (6/25/2015)


    We made following 2 changes to our database which is being used by multiple sites:

    1. We converted columns from varchar to nvarchar in all tables of our database

    2. also, during this change, we had also added below index on Stories table which is having around 1,00,000 records:

    CREATE NONCLUSTERED INDEX IX_Stories

    ON Stories (WID, SID, IsActive, SDate, EDate)

    Since above changes are made, I think our sites are running too slow. We maintain server errors in "ErrorLog" table which is getting few thousand error records inserted from past few days after the change, which has following error descriptions:

    1. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    2. The wait operation timed out

    Can above database changes cause such issue ? We are unable to trace the exact reason and hence unable to fix it.

    I tried to run below command on database, but issue is same:

    exec sp_updatestats

    Our hosting provider is saying that we have a single database on the server which is being accessed my multiple websites. These 40 websites are having same connection string which uses sa user to connect to this same database. Our hosting provider said that we should create separate database user for each site. Can this be the reason for sites running slow and how can we confirm the same?

    You can have multiple sites accessing a single database with no problem. However, connecting with the sa login is a problem. You don't ever want to have the sa login used to connect from any web site. In fact, you don't want the sa login to be enabled at all. You'll be much better off if you create a login for the application to use and grant it the specific permissions it need to work and nothing else. By using the sa login to connect from the web site, you're opening a huge door that you don't want open.

    As for the performance problem, there isn't enough information to be able to pinpoint anything specific. I would start by examining your indexes. You changed the data type of a bunch of columns, so the indexes on those columns need to be updated. This would ordinarily be covered by an index maintenance script, but I don't know what you have set up.

  • Ed Wagner (6/25/2015)


    You can have multiple sites accessing a single database with no problem. However, connecting with the sa login is a problem. You don't ever want to have the sa login used to connect from any web site. In fact, you don't want the sa login to be enabled at all. You'll be much better off if you create a login for the application to use and grant it the specific permissions it need to work and nothing else. By using the sa login to connect from the web site, you're opening a huge door that you don't want open.

    As for the performance problem, there isn't enough information to be able to pinpoint anything specific. I would start by examining your indexes. You changed the data type of a bunch of columns, so the indexes on those columns need to be updated. This would ordinarily be covered by an index maintenance script, but I don't know what you have set up.

    Great point on the 'sa' login. I blew past that, but it's extremely important, and a major problem just waiting for someone or something to exploit.

    On a guess, do you know if your applications have protection against SQL Injection? If not, you may want to ignore your performance problems for the moment and concentrate all your efforts, and those of all your development team, and all your IT team, towards resolving that, NOW.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • +1 to the rest of the comments; but there could be many reason for the timeout errors. Clearly indexes/statistics can cause performance issues....and since you changed a column data type you may want to closely examine the execution plans for the procedures being called from IMPLICIT CONVERSIONS that might have surfaced as a result of the data type change.

    Also, since you changed the data type, and index changes are involved its possible that you still have some old execution plans laying around that are using the old data types (but this change should have automatically caused them to recompile).

    In any event, have you run the procedures locally in SSMS and compared the duration to the ones running from the web? This might help pinpoint if the issue is happening from the application versus database layer.

    Perhaps post the TSQL code and execution plan from one of the problematic procedures...?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Hi,

    I used exec dbo.sp_WhoIsActive @get_plans = 1, @get_additional_info = 1 and most of the time it is showing below procedure as Blocking Procedure:

    SELECT top 20 StoryID, SectionID, Title FROM Story

    WHERE

    (SiteID = @SiteID) and

    (SectionID in (Select SectionID from Section where SiteID = @SiteID and IsStatus = 'True' and IsFeaturedStories = 'True' and SectionType = 'Story')) and

    (IsStatus = 'True') and

    (StartDate <= @TodaysDate) and

    (EndDate is null or EndDate >= @TodaysDate)

    order by PublishDate desc, UpdatedOn desc

    The story table is having 90,000 records. In activity monitor I observed Waiting Tasks sometimes reaches to 10,000 for few seconds. I tried to add below index on Story table, but it gave Wait Error sometimes while Inserting a new record in Story table:

    CREATE NONCLUSTERED INDEX IX_Story

    ON [dbo].[Story] ([SiteID],[IsStatus],[StartDate],[EndDate])

    Also, our server is having Core 2 Quad CPU with 8 GB RAM and we are currently using SQL Server 2008 R2 Express edition and thinking to upgrade it to SQL Server 2012 Web edition. Should we go for this upgrade? We are specially looking for features like Profiler so that we can monitor such slow performances which are not in Express edition?

  • i believe SQL Server 2008 R2 Express edition will limit itself to using 1 gig of ram max, regardless of the amount of ram available on the host machine.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply