SQL Server Performance Forum – Threads Archive

How to improve SQL Server 2005 performance?

Hello. I have a general question:
How to improve performance of my SQL Server 2005 database? Here some details:
– I am using SQL Server 2005 Express edition (if it can make a difference).
– I am managing a relatively very small database: about 10 tables with 3 ~ 10 columns each.
– My queries are very simple (not nested).
– I am connecting to my database through .NET C# by using SqlConnection class with connection strng and SqlCommand for executing my queries.
– I am focused on high performance of INSERT queries. SELECT, DELETE and UPDATE queries will be performed in "offline" mode and doesn’t requires high performance.
– I have measured time bfore and after calling the command to be executed (command.ExecuteNonQuery()). It took 15 millisecond.
– I read that the better for INSERT oriented databases is using pre-compiled queries – Stored Procedures (and not using additional indexation). I passed all may INSERT queries into SP, but the 15 ms. remained! This holding me on ~65 queries per second while I need at least 400 (about 2 ms. for one query). I beleave it is possible in SQL Server, but please correct me if I mistake. (I have new dual core machne, so HW is not the problem) Maybe some tuning of configurations of the SQL Server itself is required?
Or, if i will buffer some amount of new records in memory (by using DataSets) and than
will write (for example in packs of 1000 records) the whole DataSet into the DB, will
it perform better? Please help me understand what is the bottle neck and how to improve it. Any help is appreciated. Thanks in advance.
Leon.

I don’t have to much experience with SQL Express but a lot with SQL 2000 personal. Assuming the same restrictions for both products, you can’t expect great performance comparing with Standard or Higher versions. Is Express installed in you personal machine? Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

what code are you using to establish time
15ms is the OS clock resolution I think my papers on this site
show 8,000-10,000 inserts /sec with single row insert queries
30K/sec with multiple insert per call
and that was on a 2xXeon 2.4GHz (the old Xeon, not the new faster Core 2 Xeons) hardware config is very important
see my instructions on www.qdpma.com for what counters to watch
log write latency is probably most critical

]]>