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?
- Increase number of tempdb files up until number of cores? In which case, where should I place them?
- I am now profiling the load to look for index creation opportunities. Any tips on what to pay attention?
- What else?
Thanks in advance,