Still removing the rust from my SQL Server skills, so please bear with some stupid questions here and there...

Customer has a SQL Server 2008 R2 running in an Azure VM with 8 cores and 14GB RAM. Disks are organized as follows:

  • Disk E (2-stripe, 1TB partition): DBF and 1GB tempdb;
  • Disk F (2-stripe, 1TB partition): Logs and 1GB tempdb;
  • Disk G (4-stripe; small 20GB partition): 2 1GB Tempdb files;
  • Disk H (Single stripe, 500GB partition): Backup files;

Customer has introduced a couple of new processes which have resulted in an excessive PageWrites/s (daily avg 370(!), max 1000), which, on occasions, brings server performance to a crawl. Pagereads and Lazywrites are well within "best practices".

The introduced processes are 50% SELECTs, 30% UPDATEs, 20% INSERTs in nature.

The server started off with just 01 (I know...) Tempdb and I have been increasing its number of files, which improves performance, but it still doesn't bring PageWrites/s count down to the recommended 90-100.

What are the next steps?

  1. Increase number of tempdb files up until number of cores? In which case, where should I place them?
  2. I am now profiling the load to look for index creation opportunities. Any tips on what to pay attention?
  3. What else?

Thanks in advance,

  • 2
    Are TF -1117(grow all the data files equally) and 1118 (use only full extents) ON ? Are all the data files properly and equally sized to avoid any expensive autogrowths ? What are the waits happening when you say the server is crawling ? What is the MaxDop and Max Memory setting ? – Kin Shah Apr 12 '15 at 17:38
  • Identify your expansive queries and optimize them. You can do that by querying the Plan Cache or by using Profiler (puts loads on the server so watch out) – Matan Yungman Apr 13 '15 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.