Diwali (also spelled Divali in other countries) or Deepavali is popularly known as the festival of lights. It literally means “array of light”. Diwali is the most important festival of the year and is celebrated with families performing traditional activities together in their homes. Deepavali is an official holiday in India. I pretty much work every day except today. I dedicate this day to my family. This is their day.
Every year on Deepavali I share a database tips with all of my blog readers. I quite often get ask if I can help people with their systems performance. I am always willing to help people to check their system performance. If you want me to help you check your system’s performance and get early opinion from me about your system. You can send me result of following queries in excel to me.
-- Query 1 SELECT * FROM sys.configurations GO -- Query 2 SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC GO
Once I receive your result, I will get back to you with proper first glance suggestions. May be your performance problem is very much visible but you have not paid attention to it.
Here are few tricks which always worked
- Update your statistics at regular interval
- Keep your fill factor between 60 to 90 based on your business need
- At regular interval keep up your indexes and remove fragmentation
- Use Stored Procedure instead of ad-hoc queries
- Reduce usage of cursors
- Avoid unnecessary usage of views and triggers
- Take back up at regular interval and attempt to restore it often to test the recovery process
Happy Diwali to All of You!
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
Happy Diwali Pinal!
Keep up your fantastic blog.
Greetings,
Michael
So my TOP 5
LAZYWRITER_SLEEP 4723607 653829515 686532 2124482
XE_TIMER_EVENT 11067 329998369 31381 329997912
XE_DISPATCHER_WAIT 409 329993046 32176818 0
REQUEST_FOR_DEADLOCK_SEARCH 65570 329777222 6873 329777222
SQLTRACE_INCREMENTAL_FLUSH_SLEEP 81856 329274834 6680 3992
this is from my favorite cluster ;)
So can U provide also best practice help how I need to do: i have cluster with 2 nodes active-passive schema, and 2 servers more will arrived soon – I can add this 2 nodes to existing cluster or organize standalone cluster like I allready have (2 nodes, active-passive, one node master of clust-MSSQL1\IN1, other clust-MSSQL2\IN2) In case of 4 nodes cluster I will have very large HA, because if 2 nodes will die all DBs can works on 2 good nodes. I really cannot find any best practice information in google about clustering. Thks
Happy Diwali Pinal. Take Care
Happy Diwali Pinal!!
Here´s my output — suggest to copy to an excel sheet :)
configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
101 recovery interval (min) 0 0 32767 0 Maximum recovery interval in minutes 1 1
102 allow updates 0 0 1 0 Allow updates to system tables 1 0
103 user connections 0 0 32767 0 Number of user connections allowed 0 1
106 locks 0 5000 2147483647 0 Number of locks for all users 0 1
107 open objects 0 0 2147483647 0 Number of open database objects 0 1
109 fill factor (%) 0 0 100 0 Default fill factor percentage 0 1
114 disallow results from triggers 0 0 1 0 Disallow returning results from triggers 1 1
115 nested triggers 1 0 1 1 Allow triggers to be invoked within triggers 1 0
116 server trigger recursion 1 0 1 1 Allow recursion for server level triggers 1 0
117 remote access 1 0 1 1 Allow remote access 0 0
124 default language 0 0 9999 0 default language 1 0
400 cross db ownership chaining 0 0 1 0 Allow cross db ownership chaining 1 0
503 max worker threads 0 128 32767 0 Maximum worker threads 0 1
505 network packet size (B) 4096 512 32767 4096 Network packet size 1 1
518 show advanced options 1 0 1 1 show advanced options 1 0
542 remote proc trans 0 0 1 0 Create DTC transaction for remote procedures 1 0
544 c2 audit mode 0 0 1 0 c2 audit mode 0 1
1126 default full-text language 1033 0 2147483647 1033 default full-text language 1 1
1127 two digit year cutoff 2049 1753 9999 2049 two digit year cutoff 1 1
1505 index create memory (KB) 0 704 2147483647 0 Memory for index create sorts (kBytes) 1 1
1517 priority boost 0 0 1 0 Priority boost 0 1
1519 remote login timeout (s) 20 0 2147483647 20 remote login timeout 1 0
1520 remote query timeout (s) 0 0 2147483647 0 remote query timeout 1 0
1531 cursor threshold -1 -1 2147483647 -1 cursor threshold 1 1
1532 set working set size 0 0 1 0 set working set size 0 1
1534 user options 0 0 32767 0 user options 1 0
1535 affinity mask 0 -2147483648 2147483647 0 affinity mask 1 1
1536 max text repl size (B) 131072 0 2147483647 131072 Maximum size of a text field in replication. 1 0
1537 media retention 0 0 365 0 Tape retention period in days 0 1
1538 cost threshold for parallelism 5 0 32767 5 cost threshold for parallelism 1 1
1539 max degree of parallelism 0 0 64 0 maximum degree of parallelism 1 1
1540 min memory per query (KB) 1024 512 2147483647 1024 minimum memory per query (kBytes) 1 1
1541 query wait (s) -1 -1 2147483647 -1 maximum time to wait for query memory (s) 1 1
1543 min server memory (MB) 0 0 2147483647 0 Minimum size of server memory (MB) 1 1
1544 max server memory (MB) 6600 16 2147483647 6600 Maximum size of server memory (MB) 1 1
1545 query governor cost limit 0 0 2147483647 0 Maximum estimated cost allowed by query governor 1 1
1546 lightweight pooling 0 0 1 0 User mode scheduler uses lightweight pooling 0 1
1547 scan for startup procs 0 0 1 0 scan for startup stored procedures 0 1
1548 awe enabled 1 0 1 1 AWE enabled in the server 0 1
1550 affinity I/O mask 0 -2147483648 2147483647 0 affinity I/O mask 0 1
1555 transform noise words 0 0 1 0 Transform noise words for full-text query 1 1
1556 precompute rank 0 0 1 0 Use precomputed rank for full-text query 1 1
1557 PH timeout (s) 60 1 3600 60 DB connection timeout for full-text protocol handler (s) 1 1
1562 clr enabled 0 0 1 0 CLR user code execution enabled in the server 1 0
1563 max full-text crawl range 4 0 256 4 Maximum crawl ranges allowed in full-text indexing 1 1
1564 ft notify bandwidth (min) 0 0 32767 0 Number of reserved full-text notifications buffers 1 1
1565 ft notify bandwidth (max) 100 0 32767 100 Max number of full-text notifications buffers 1 1
1566 ft crawl bandwidth (min) 0 0 32767 0 Number of reserved full-text crawl buffers 1 1
1567 ft crawl bandwidth (max) 100 0 32767 100 Max number of full-text crawl buffers 1 1
1568 default trace enabled 1 0 1 1 Enable or disable the default trace 1 1
1569 blocked process threshold 5 0 86400 5 Blocked process reporting threshold 1 1
1570 in-doubt xact resolution 0 0 2 0 Recovery policy for DTC transactions with unknown outcome 1 1
1576 remote admin connections 1 0 1 1 Dedicated Admin Connections are allowed from remote clients 1 0
1577 common criteria compliance enabled 0 0 1 0 Common Criteria compliance mode enabled 0 1
16384 Agent XPs 1 0 1 1 Enable or disable Agent XPs 1 1
16385 SQL Mail XPs 0 0 1 0 Enable or disable SQL Mail XPs 1 1
16386 Database Mail XPs 0 0 1 0 Enable or disable Database Mail XPs 1 1
16387 SMO and DMO XPs 1 0 1 1 Enable or disable SMO and DMO XPs 1 1
16388 Ole Automation Procedures 1 0 1 1 Enable or disable Ole Automation Procedures 1 1
16389 Web Assistant Procedures 0 0 1 0 Enable or disable Web Assistant Procedures 1 1
16390 xp_cmdshell 1 0 1 1 Enable or disable command shell 1 1
16391 Ad Hoc Distributed Queries 0 0 1 0 Enable or disable Ad Hoc Distributed Queries 1 1
16392 Replication XPs 0 0 1 0 Enable or disable Replication XPs 1 1
CXPACKET 630177333 36068291218 38233031 1048090453
DBMIRRORING_CMD 28689494 16890154703 19062546 35577343
PAGEIOLATCH_SH 885377446 14194616281 54625 318710156
Awesome stuff Pinal. Just started reading your blogs and getting your emails. I am a “jack of all trades” developer – MS Access, VB.NET, C#.NET, SQL Server as I am a 1 man show at my current company. I have decided to concentrate on the RDBMS aspect of things and find your information extremely helpful.
I was going to paste my stats in here but it was atrocious looking and you would never be able to figure it out. Is there a way I can send you the stats in a neatly formed Excel spreadsheet?
Thanks for your great work.
Keith
Sure send to me via email address and I will be happy to answer.