Have Geek Brain

SQL Server Express performance issue

I know my questions are going to sound silly and probably no one will have a perfect answer, but since I am completely stumped with the situation, it will become easier for me to post it here.

So...

I have a SQL Server Express database that is 500MB. It contains 5 tables and possibly 30 stored procedures. This database is used to store articles and is used for the Developer It website . Typically, web pages load quickly, say 2 or 3 seconds. BUT, the sqlserver process is using 100% CPU for those 2 or 3 seconds.

I'm trying to find which stored procedure was the problem and I couldn't find it. It seems like every dans read in the table contains articles (about 155,000 of them and 20 or so are added every 15 minutes).

I added some indices but no luck ...

Is it because the table is indexed with full text? Should I have an order with a primary key instead of a date? I've never had a problem with ordering by dates .... Should I use dynamic SQL? Should I add the primary key to the article url? Should I use multiple indexes for individual columns, or one large index?

I need more details or bits of code, just ask for it.

Basically, every little hint is greatly appreciated.

Thanks.

+2


a source to share


2 answers


If your index is not used, it usually indicates one of two problems:

  • Non-transitive predicate conditions such as WHERE DATEPART(YY, Column) = <something>

    . Wrapping columns in a function can damage or eliminate the optimizer's ability to efficiently use the index.

  • Non-overlapping columns in the output list, which is very likely if you're used to writing SELECT *

    instead SELECT specific_columns

    . If the index does not cover your query, SQL Server must perform RID / key lookups for each row one by one, which can slow down the query so much that the optimizer simply decides to scan the table.



See if one of them might apply to your situation; if you are still confused, i would recommend updating the question with more information about your schema, data and slow queries. 500MB is very small for a SQL database, so it shouldn't be slow. Also post what's in the execution plan.

+3


a source


Use SQL Profiler to capture many of the typical queries used in your application. Then run the profiler results using the Index Tuning Wizard. This will tell you which indexes can be added for optimization.



Then look at the worst-case queries being executed and manually analyze their execution plans.

+2


a source






More articles:


All Articles