Tech Off Thread

14 posts

SQL Express performance decreasing...

Back to Forum: Tech Off
  • User profile image
    jh71283

    Each of the computers that my app runs on has a SQL express database.

    However, it seems that over the course of a few months, the performance of the database seems to be degrading, with occasional timeouts etc too.

    I have tried running the expressmaint utility, using both REINDEX and REORG, and I am waiting to hear whether that has been successful from my sites.

    Should the utility make a difference? Also, is there anything else I should be running against these databases to keep the performance optimal?

    The application is pretty hard on the database, It can be running a few thousand inserts throughout the day, along with lots of queries, and deletes....

  • User profile image
    figuerres

    jh71283 wrote:
    Each of the computers that my app runs on has a SQL express database.

    However, it seems that over the course of a few months, the performance of the database seems to be degrading, with occasional timeouts etc too.

    I have tried running the expressmaint utility, using both REINDEX and REORG, and I am waiting to hear whether that has been successful from my sites.

    Should the utility make a difference? Also, is there anything else I should be running against these databases to keep the performance optimal?

    The application is pretty hard on the database, It can be running a few thousand inserts throughout the day, along with lots of queries, and deletes....


    some info you need to provide and think about:

    database size ??
    CPU and ram of the pc running the sql express ??
    are all connections local ??
    if network users how many ??

    what kind of operations are having problems ??

    in tables that are timing out how many records do they have??
    what is the growth pattern of the tables??
    ( ex:  doubling in size, growing by 10% etc )

    your issue may be as simple as adding a few indexes.

    hard to say w/o more details.

    use the sql query window in the express manager gui
    look at "estimated execution plan"
    do you know how to use this tool??

    if not later I can post more about how to use it to find out where your query is getting stuck.

  • User profile image
    Maddus Mattus

    jh71283 wrote:
    Each of the computers that my app runs on has a SQL express database.

    However, it seems that over the course of a few months, the performance of the database seems to be degrading, with occasional timeouts etc too.

    I have tried running the expressmaint utility, using both REINDEX and REORG, and I am waiting to hear whether that has been successful from my sites.

    Should the utility make a difference? Also, is there anything else I should be running against these databases to keep the performance optimal?

    The application is pretty hard on the database, It can be running a few thousand inserts throughout the day, along with lots of queries, and deletes....


    Out of diskspace? Fragmented disk?

  • User profile image
    axelriet

    It sounds like your app is running on workstations. Are your database files heavily fragmented? Have you tried to shut down your app and SQLExpress (just stop the service) then thoroughly defrag the drive?

    If you have two (or more) physically distinct drives in the systems, have you tried to move the database logs (*.ldf) on a different drive than the data files (*.mdf)? Putting the transaction logs and data files on the same physical drive is a huge performance no-no!

  • User profile image
    jh71283

    Hey all thanks for your replies.

    The database size is typically 20MB or under, and connections are always local.

    Database access is typically done via ADO.net / datasets.

    The data that is being entered is transaction data, consisting of a few hundred Transaction rows, and a few thousand 'TransactionItem' rows. These tables are completely cleared at the end of each day.

    The typical spec of the machines (they are epos terminals) is around 1.5Ghz Celeron M, 512Mb RAM.

    Having more than one physical disk is not possible on these systems.

    I do have to admit to being a bit behind when it comes to database knowledge....

  • User profile image
    Maddus Mattus

    You could create a workload file and run the index tuning wizard,..

  • User profile image
    figuerres

    jh71283 wrote:
    

    Hey all thanks for your replies.

    The database size is typically 20MB or under, and connections are always local.

    Database access is typically done via ADO.net / datasets.

    The data that is being entered is transaction data, consisting of a few hundred Transaction rows, and a few thousand 'TransactionItem' rows. These tables are completely cleared at the end of each day.

    The typical spec of the machines (they are epos terminals) is around 1.5Ghz Celeron M, 512Mb RAM.

    Having more than one physical disk is not possible on these systems.

    I do have to admit to being a bit behind when it comes to database knowledge....



    so the week to week size of the database is about the same ??

    starting to sound like a defrag-the-disk problem as top suspect.

    are there any tables that are growing and are used often??
    if so look at index tuning to improve perf.

    tables tend to look ok till you get a large set of rows then you start to see where they slow down.

    if you have some tables that have grown you may be doing table scans to find rows / update rows .... if that's the case an index might fix things.

    "table scan" refers to how the SQL engine has to find data.

    best is "Index Seek"
    then "Index Scan"
    then "Table Scan"
    in general that's the order of fastest lookup to slowest.

  • User profile image
    jh71283

    Yeah the database file tends not to grow over time, the longest data survives in there is 30 days.

    I'll run a few typical queries through query analyser and see what type of scans are taking place, but I think it will be table sacns, as I have not explicitly defined any indexes in the database.

    Would doing table scans all the time affect the level of fragmentation too?

  • User profile image
    jh71283

    Hmm... where does "Clustered Index Scan" fall into the above in performance?

  • User profile image
    jh71283

    OK, I've tried adding a nonclustered index to one of my tables, and set the wehere clause to only look at that column for now.

    However, I still only get table scans.

    Must the index be clustered? What is the difference?

  • User profile image
    ZippyV

    Don't forget to recompile the Stored Procedure using that index otherwise it will use the execution plan from the cache.

  • User profile image
    figuerres

    jh71283 wrote:
    OK, I've tried adding a nonclustered index to one of my tables, and set the wehere clause to only look at that column for now.

    However, I still only get table scans.

    Must the index be clustered? What is the difference?



    Ok a bit of SQL 101 for you:  Smiley


    by  default a table's rows can be in any order.

    thus the use of the "Order By"

    an Index is like a small table that has some data and a pointer to the row (or block of rows) that has that data.
    (simplified description -- ok?)

    MS SQL server will generaly create a "Clustered Index" on the Primary Key of a Table.

    what this means is that it will store the index and the rows in the same "order"

    this has several implications....

    a search / select  that uses the PK will tend to be very fast.
    rows will tend to come back in that order when no "Order by" has been given.
    New rows will be inserted on the "Last page" of the table's data pages that are open.
    re-indexing the table and other maint. tasks have to move rows to keep that "Clustered Sequnece" going.

    do your tables have any Primary Keys??

    if not then you may need to re-work somethings .....


    the SQL engine and the RDBMS model work off of the use of the table relationships PK===>FK and this impacts how well it can process a request for a given set of rows and many other things.

    when you join tables in a select and sql finds a PK->FK relationship it uses the indexes to make the processing faster.

    does this all make sence so far?

    do you know what a "Normal form" is ?

    how is your data structured ??

    a 20 meg db should not be slow ... I suspect you need to revise the db to get 3rd Normal form or something close to it.

    this also impacts your DLINQ issues by the way....
    and your dataset speeds as well.
    you might be able to get a *LOT* faster on stuff.



  • User profile image
    Antitorgo

    I'd recommend you buy a good SQL Server book and read through it. It should explain a lot of the DB performance issues to you. One thing to remember is that SQL works best when doing things in Sets vs. One-at-a-time.

    First and foremost, look at where your performance is bottlenecking (SQL Profiler is where you should start out). For a 20MB database, you shouldn't be having the problems you describe.

    Some things I'd suggest you look at first:
    If you are doing a lot of inserts/deletes, remember that those are LOCKING the table. So SELECTs by default will wait for the LOCK (unless you are using the NOLOCK hint or set your TRANSACTION ISOLATION READ UNCOMITTED).
    Are you reindexing when you insert/delete a bunch of rows (look at percentage of table as an indicator here). If you are deleting all the rows from the table regularly, think abour DROPing the table vs. DELETEs (this can help fragmentation).
    Also, if you are inserting a bunch of rows into an empty database, think about dropping the non-clustered index before the inserts and then re-creating the non-clustered indexes*
    If you are doing a lot of insert/deletes in a non-clustered indexed table, that will cause all sorts of page fragmentation and performance will suffer over time.

    Clustered Indexes vs. Heaps:
    As far as clustered indexes go, it comes down to how SQL stores things internally.

    Heaps:
    When you create a table without any index, it is be default a "heap". When you insert rows, it is stored internally in whatever order (typically the order it was inserted or if a deletion occurred, it can "fill in the hole").

    Clustered Index:
    When you create a clustered index for a table (you can only have one per table). Data is stored in the order of the primary key on the clustered index. For performance reasons, you typically want the primary key to be a unique monotonically increasing value (for example an IDENTITY column). **
    So, getting back to things a little bit, depending on how WIDE your table is and how many columns you want in your result set -- having a Clustered Index SEEK in your query can be a good thing. When you seek, it will read the entire record from disk. (if you only need one or two columns and your table is 200 columns wide, then there are better options ***).

    Non-Clustered Index:
    A non-clustered index is like a lookup table. It stores the primary keys and then a pointer to the Clustered Index or Heap Index where the full record resides. Non-clustered indexes are where you will typically do query optimizations, but for each index you add to a table, it will slow down insert/update/delete performance (since SQL has to do more work). So it can come down to being a balancing act. Also, it is important to remember that you can have multiple columns as your key in the index. Order does matter in multi-column indexes and for performance you would want the most unique column to be first in the list (Example, city vs. state in address records).

    So, lets get back to query analyzer for a second:
    Ideally, you want your queries to be SEEKs and order of performance generally goes like this (best performing to lowest)
       Clustered Index Seek/Index Seek (Depending on table width and rows returned)
       Index Scan
       Clustered Index Scan
       Table Scan

    Now, generally it is REALLY BAD to try and force the query to use any particular index. This goes doubly so if you don't know what you are doing. The SQL engine usually does a really good job at optimizing queries. I highly recommend not trying to use INDEX HINTS in your queries.

    So, it comes down to "How do you get a query to do index seeks?"
    Well, there are lots of rules around this, but a general guideline is you want your where clause to be around "=" vs. "<>".

    If you think about indexes as lookup tables. It is easier to look for existence than non-existance (<> will have to look through the entire index to see if it is there thus an index scan).

    Also, avoid the "OR" statement. This is almost a guaranteed index scan. If you have a WHERE clause that looks like:
    WHERE LastName = 'Jones' OR LastName = 'Smith'
    you might want to look at rewriting it to:
    WHERE LastName IN ('Jones', 'Smith')

    The LIKE statement is "Tricky".
    Using LIKE 'Smith%' should result in index seeks.
    Using LIKE '%Smith%' will result in an index scan.

    Watch out for CONVERT/CAST
    SQL can be smart about CONVERT/CAST on operators, but you can cause an index scan to occur because of them. The reason being that the CONVERTED/CAST data isn't in the index. If you use CONVERT/CAST, do it on the non-table data.
    Using CONVERT(VARCHAR(30), table.dtDue) = '1/31/2008' will result in Index Scans
    Using table.dtDue= CONVERT(DATETIME, '1/31/2008') will result in Index Seeks
    Note: SQL can be smart about conversions.
    Using table.dtDue = '1/31/2008' will also result in Index Seeks

    ------------------------------------

    *Note: This is a quicker operation because it is working across a set of data instead of individually when the inserts occur. Another option is to do your data loading into a TEMP table and then do an INSERT from the temp table into the actual table. (Think Sets vs. Individual)

    **Note: a GUID is actually a typically bad choice because it is random, not monotonically increasing causing paging issues, thus why the NewSequentialID() function was introduced for SQL 2005.

    ***Note: This is where a "Covered Index" comes into play. If you want to learn more, I suggest searching for that term.

  • User profile image
    PerfectPhase

    As this slows down over time without the dataset growing but with rapid turnover of data in the db, how big are your log files?  How often do you backup the database?  See here for ideas.

    EDIT: forget that, I see you've already done it.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.