This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is the use of Service Broker?
Service Broker helps developers build asynchronous, loosely coupled applications in which independent components work together to accomplish a task. These application components exchange messages that contain the information that is required to complete the task.
Service Broker can be useful for any application that needs to perform processing asynchronously, or that needs to distribute processing across a number of computers.
What are the main components of Service Broker?
- Conversation components: Conversation groups, conversations, and messages form the run-time structure of a Service Broker application. Applications exchange messages as part of a conversation.
- Service definition components: These are design-time components that specify the basic structure of the conversations that the application uses.
- Networking and security components: These components define the infrastructure used to exchange messages between instances of the Database Engine. To help database administrators manage
- Changing environments: Service Broker lets administrators configure these components independently of the application code.
What are the advantages of Service Broker?
Database integration enhances application performance and simplifies administration.
Message ordering and coordination for simplified application development
Loose application coupling provides workload flexibility.
Related message locking allows more than one instance of an application to process messages from the same queue without explicit synchronization.
Automatic activation allows applications to scale with the message volume.
I have a database which has several tables that have very heavy write operations. These table are very large and some are over a hundred gigabytes. I noticed performance of this database is getting slower and after some investigation we suspect that the Auto Update Statistics function is causing a performance degradation.
What should be your solution of this problem?
Statistics are objects which contain information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses this statistical data to estimate the number of rows in the query result. In the case where the statistics are invalid or outdated then the query optimizer is likely to choose a wrong execution plan and query performance will decrease. Therefore keeping statistics up to date is an essential part of SQL Server maintenance.
If we experience problems due to statistics automatically being updated then the solution might be to disable the Auto Update Statistics option. However, we cannot live with outdated statistics on large databases, so if you turn this option off you should create a scheduled job which explicitly updates the statistics during off-peak periods. To do this, we can use the sp_updatestats procedure.
What are the different type of Fragmentations?
- Logical/Phsical Disk Fragmentation
- Extent Fragmentation
- File Fragmentation
- Page Fragmentation
- Index Fragmentation
What are the index statistics?
Index statistics contain information about the distribution of index key values. By distribution, I mean the number of rows associated with each key value. SQL Server uses this information to determine what kind of execution plan to use when processing a query.
When are index statistics updated?
The AUTO_UPDATE_STATISTICS database setting controls when statistics are automatically updated. Once statistics have been created, SQL Server then determines when to update those statistics based on how out-of-date the statistics might be.
SQL Server identifies out of date statistics based on the number of inserts, updates, and deletes that have occurred since the last time statistics were updated, and then recreates the statistics based on a threshold. The threshold is relative to the number of records in the table. (Enable the properties – “auto create statistics” and “auto update statistics” for OLTP)
Explain database options “Auto Update Statistics” and “Auto Update Statistics Asynchronous”?
Auto Update Statistics: If there is an incoming query but statistics are stale then sql server first update the statistics before building the execution plan.
Auto Update Statistics Asynchronous: If there is an incoming query but statistics are stale then sql servers uses the stale statistics, builds the execution plan and then update the statistics.
How to update statistics manually?
If you want to manually update statistics, you can use either sp_updatestats or UPDATE STATISTICS <statistics name>
What are the various types of statistics available?
There are three different types of statistics available.
- Statistics created due to index creation.
- Statistics created by optimizer.
- User defined statistics created from “CREATE STATISTICS”
What are the various options to be considered while designing a new execution plan?
There are a number of factors that influence the decision in choosing the proper execution plan. One of the most important ones are cardinality estimations, the process of calculating the number of qualifying rows that are likely after filtering operations are applied.
A query execution plan selected with inaccurate cardinality estimates can perform several orders of magnitude slower than one selected with accurate estimates. These cardinality estimations also influence plan design options, such as join-order and parallelism. Even memory allocation, the amount of memory that a query requests, is guided by cardinality estimations.
How to find out when statistics updated last time?
A simple logic is, run the query and observe the values for both “estimated rows” and “actual rows”, if they both are close to each other you need not worried about the statistics. If you find big difference between them then you need to think about updating statistics.
In general we can find out last statistics updated info from below query
What are the statistics in SQL Server?
Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.
In which situation you should update statistics?
If there is significant change in the key values in the index
If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
Database is upgraded from a previous version.
What are the steps to improve the performance of a query?
- Number of joins and use of complex views/cursors have to be reduced.
- The use of the stored procedures and indexes have to be maximized.
- The optimized use of the complex conditional checks and computer columns have to be in place.
- Tracking of performance analysis for the query helps us in identifying the right aspects to optimize.
Could you please give some Optimization Tips in writing SQL Queries ?
- Always try to use views and stored procedures instead of doing work with heavy queries.
- Make a habit to use constraints instead of triggers whenever it is possible.
- When you need n number of row from database try to use top keyword or SET ROWCOUNT statement
- Always use table variables in place of temporary tables.
- Avoid Union and try to use UNION ALL statement.
- Always avoid using the DISTINCT clause, whenever possible.
- Always try to avoid using SQL Server cursors.
- Always try to avoid the HAVING clause.
- Do not use select count(*) to get number of rows
- Try to include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
- Always use file system to store large binary objects and use the file path in database.
- Sometimes we may have to apply more than one sub queries in our main query. Try to minimize the number of sub query block in your query
