Performance Tuning- Question and Answer

  • It is a process of ensuring that the SQL statements issued by an application run in the fastest possible time.
  • By tuning the performance of the queries, users benefit as they get faster results and can take quick decisions.
  • The general goal of SQL performance tuning is to decrease response time, increase throughput.
  • The unique key improves table scan performance.
  • Choose a table scan for small records and seek-scan for numerous records.
  • Keep index size as small as possible.
  • Use an indexed view for aggregated SQL queries.
  • Use the covering index to reduce RID (Row Identifier) lookup.
  • Prefer to use views and stored procedures for writing long queries.
    It will also help in minimizing network load.
  • Make use of the SET NOCOUNT ON clause while building stored procedures. It reduces network traffic.
  • RID is a row locator that includes information about the location of that record such as the database file, the page, the slot numbers that helps to identify the location of the row quickly.
  • If the table does not have a clustered index then a RID lookup occurs instead.
  • The key lookup operator is the clustered equivalent of the RID lookup operator.
  • By execution plan, we can know why a given query is performing thousands of scans and we can modify your query accordingly and make it fast.
  • The aggregate expressions include the MIN, MAX, COUNT, AVG, SUM operations
  • By using the server type and server name connect to the SQL Server Profiler.
  • In the trace property window, select a template and provide a database name in the column filter option.
  • Finally, trace files for query optimization to click on the run button. Save the trace file.
  • In the general tab, select the file, and database. Click on Start Analysis.
  • In the report tab, we can see suggestions like Statement cost report, Event frequency report, Index detail report, and so on.
  • DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. It serves to the empty data cache.
  • When STATISTICS IO is ON, Statistical information is displayed, and when OFF, the information isn’t displayed.
  • SET STATISTICS TIME displays the amount of time needed to parse, compile, and execute each statement in the query.
QUERY PLAN
  • SQL parser, SQL optimizer, and a source code generator compile the SQL statement.
  • Cost-based optimizer
  • When data read on cache then it called logical read.
  • Fragmentation means your physical data and indexes are out of synchronous storing data non-contiguously on disk is known as Fragmentation.
  • We can detect fragmentation by using system function sys.dm_db_index_physical_stats.
  • To remove fragmentation goto object explorer ab=nd open database as well table. Right-click on the fragmented index and select REORGANIZE or REBUILD. Then click on ok.
  • Rebuild locks the table for the whole operation period and Reorganize doesn’t lock the table.
  • Rebuilds one or more indexes for a table in the specified database. Reorganize index defragments clustered and secondary indexes of the specified table.
  • Include Keyboard
  • The size of the index increases the key size which further impacts performances.
  • It can exceed the constraint of the index key size of 900 bytes.
  • By using the extended events, we can see details about the inner operations of the SQL system and your application.
  • It was a very useful tool to see in real-time what SQL queries are being executed against your database.
  • system_health
  • telemetry_xevents
DEFAULT SESSIONS
  • It is easier to troubleshoot a stored procedure than inline queries.
  • If the plan selected from the cache and find procedure then SP:CacheHit event occurs.
  • There are different nature of workloads like heavy, light workloads.
  • If heavy workload takes all the memory from CPU and because of that other operations to get affected then it is a very bad thing.
  • Resource governor is used for how much memory and CPU should be allocated to the resources.
  • We can quickly recall a procedure cache. Procedure cache also stores the latest execution plan only.
  • These plans stored in the procedure cache will be wiped out due to memory pressure which is not the case with the query store.
  • In the database property, goto query store option.
  • In the general tab, set Operation Mode is off and Operation Mode is ReadWrite. Then click on ok.
  • We can use the Database Engine Tuning Advisor to analyze the performance implications.
  • Use partitioning to avoid large data moves.
  • Use the EXISTS command instead of DISTINCT when using table joins for tables with one-to-many relationships.
  • Select appropriate data type and use the appropriate naming conventions.
  • Use normalization, primary key, and table-valued functions.
  • Create and use the index.
  • Don’t count all rows if you only need to see if data exists.
  • Try not to use having a clause in select statements.
  • Avoid using updates instead of a case.
  • Never Use ”Sp_” for users to define the stored procedures.
  • The most left SELECT operator contains the final result of the submitted query.
  • When query takes a long time to execute and we need to troubleshoot it then SQL execution plan is needed.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store