SQL SERVER Performance and Scalability Issues

Why SQL SERVER Database Performance and Scalability Issues arise that affect the performance and the scalability of application especially in multiuser system. What are the main reasons?

Views: 3209 | Community Opinion: 2

Tags..  SQL SERVER Optimization  Database

Bookmark this page..

Ask a New Question Go to Home

Community Opinion/Answers

Here are some main reasons of SQL SERVER Database Performance and Scalability Issues

Inefficient schemas. Adding indexes can help improve performance. However, their impact may be limited if your queries are inefficient because of poor table design that results in too many join operations or in inefficient join operations. Schema design is a key performance factor. It also provides information to the server that may be used to optimize query plans. Schema design is largely a tradeoff between good read performance and good write performance. Normalization helps write performance. Denormalization helps read performance.

Retrieving too much data A common mistake is to retrieve more data than you actually require. Retrieving too much data leads to increased network traffic, and increased server and client resource use. This can include both columns and rows.

Misuse of transactions Long-running transactions, transactions that depend on user input to commit, transactions that never commit because of an error, and non-transactional queries inside transactions cause scalability and performance problems because they lock resources longer than needed.

Not knowing the performance and scalability characteristics of your system If performance and scalability of a system are important to you, the biggest mistake that you can make is to not know the actual performance and scalability characteristics of important queries, and the effect the different queries have on each other in a multiuser system. You achieve performance and scalability when you limit resource use and handle contention for those resources. Contention is caused by locking and by physical contention. Resource use includes CPU utilization, network I/O, disk I/O, and memory use.

Misuse of indexes If you do not create indexes that support the queries that are issued against your server, the performance of your application suffers as a result. However, if you have too many indexes, the insert and update performance of your application suffers. You have to find a balance between the indexing needs of the writes and reads that is based on how your application is used.

Mixing OLTP, OLAP, and reporting workloads OLTP workloads are characterized by many small transactions, with an expectation of very quick response time from the user. OLAP and reporting workloads are characterized by a few long-running operations that might consume more resources and cause more contention. The long-running operations are caused by locking and by the underlying physical sub-system. You must resolve this conflict to achieve a scalable system.

Using an inefficient disk subsystem The physical disk subsystem must provide a database server with sufficient I/O processing power to permit the database server to run without disk queuing or long I/O waits.

Surendra Said..

Following up on Kyle's comment.While we've alyaws had some plain SQL in our app, there has been a definitely trend away from LINQ in the last year or so. Places where we've alyaws used SQL are generally those where we needed some construct you can't (easily) express in LINQ, or where the fetch and update pattern of LINQ imposed too much overhead.A tiny part of the migration was caused by the SELECT * problem, although that's easy to solve with LINQ it's also rather easy to forget to. The rest was actually the CPU cost of LINQ generating the query SQL and deserializing the resulting rows.

What do you think? Add your opinion/answer
*your opinion/answer: