Software

Ask something
Discussion Locked

SQL Server Performance

0 helpful
6 replies

by dun-no

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Any other solutions to improve performance besides adding RAM? I have an application that accesses SQL 2000 running on a dual Xenon 2.6ghz processor with 2 gigs of RAM. DB size is ~ 9 gigs uncompressed, sometimes access is immediate, sometimes it can take 3 or more minutes. I can't say it's a network problem as even if I run the application on the SQL server I can get varying response times.

6 replies

by ahar

this sounds as if you have a weak implementation of a database-schema. Maybe you think over your indices and relations. Use the query-analyzer and SQL-profiler to check where the delays come from.

by blj

Usually the performance comes with a good design of the database.
1. Use appropriate field types. For e.g. use tinyint, smallint wherever it is applicable.
2. Use smalldatetime instead of datetime if you dont need much precision.
3. Use decimal instead of currency, if the whole application is using a single currency.
4. Use "bit" datatype for boolean values, instead of using char(1) to store 'Y' or 'N'. I have seen people using char(3) for 'YES, 'NO'

5. Primary index does not neccessarily have to be the clustered. For e.g. a table which stores the login name and password can have login name as clustered index as that will be used to find the user first, instead of the small number based id fields ( which is purely used for relational purpose ). Also remember marking a field clustered might be a problem while inserting records with a massive number of records.
6. Use numbers instead of character strings to form relationships.
7. Redesign the schema after your experience with the application. First cut usually tends to be flexible. For e.g. if we know a field which stores big integer values but only a small range, then it is better storing the offset from the first value.
8. Use stored procedures.
9. Start the transaction only when you need it and exit out of transaction ASAP. for e.g. Don't start your transaction until you have done your validation etc. in your stored procedure.
10. Don't store files inside the database unless it is absolutely necessary. For e.g. storing the image files in the database. Avoid storing the path and filenames, instead try to follow a filename scheme based on the unique fields of the record which the file is related to.
All the above are small things and usually a common sense. Sorry to those who didnt learn anything new from the above list, but I hope atleast somebody new will benefit.

by jaqui

the database design is slowing it down.

the slow responses are long searches, which means either unusually difficult set of data, or huge amount of results.

by tony hopkinson

Have a look at optimization.
Just adding an index can be a massive performance increase.
Can't tell you where to look without a full schema and details of the application.

But find the queries used where you are geting the delays and use the 'showplan' facility to have a look. It's a bit opaque sometimes, but if you google a few articles on optimizing queries, you'll get some clues on where to start.

Things to look for are,if you have unindexed columns in where clauses, and two pass sorts. e.g. sorting a result set on fields that aren't in your where clause.

DBMS's are quie clever in terms of caching previous answers, so you get a delay the first time, and then a quick response after, which could explain the varying response times your are getting.

Querying remote servers, is also a good one. If changes are involved, then locking, triggers etc can be a problem. More memory won't hurt, but from the sound of it I doubt it's going to solve the real problem.

It could be the application itself though, particularly if it's pulling in a mass of data and then analyzing it, instead of the DBMS doing the work.
The other possibility is parsing.
Seen problems with this scenario.
Application needs a list of orders for a customer.
To populate each order, it gets a list of all the orders for the customer, and then runs a query for each order to get the details. 40% ish of the time spent the DBMS spends is parsing the SQL. So if the system had run one query for all the order details for a customer, it could have been minutes faster.

by codejock

Had a similar problem, though the response times didn't vary so widely.

The suggestions by others are definitely worth looking into.

In our particular problem, we had a table containing over 5.2 million rows of electrical meter ids and their physical addresses. All it took was a change to the clustered index. We stopped indexing on the row key and indexed on meter id and address. Even with indices on the two fields, the performance was awful.

Our response time dropped from 180 seconds on average to between 32 and 96 milliseconds. Our database actually routinely smoked the state's public utilities commission's searches.

(We couldn't make the meter id a Primary Key because there were duplicates within the utility company datasets; one could have a status 'Active' and another a status of 'De-activated' and another 'Destroyed'. CSVs built from legacy systems. UGH)

As suggested by jacqui and others, optimization, schema, triggers, normalization/de-normalization and other performance-impacting items should be examined.

Would be interested in hearing how you fared, and what the solution turned out to be, dun-no. (Without giving up the store of course :D)

by mirrormirror

I agree with the above responses. You should run Profiler and capture all statements that run when you experience a slowdown. Find the query with the long duration and start tracking back to what other statements/processes could affect the long running one. Could it be that there is another process that is holding a table lock? Take any long running statements and test them and use the Index Tuning Wizard to help evaluate the indexes you might need. You can download a trial of Idera's SQL Diagnositic Manager and watch spids to see how much CPU they are consuming. Then you will know what spid to watch with Profiler.

You also need to check some of your configuration in SQL Server. Could it be that the db is automatically growing or shrinking when you experience the slowdown? Is a backup happening right at that time? Those things can cause you system to have a slow response at unexpected times.

You can find lots of good info at: http://www.sql-server-performance.com/

This discussion is closed to new replies.