MSQL SQL Server E-S

[jjunglaus]

SQL Server found 1 instance(s) of I/O requests taking more than 15 seconds to complete in file [C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\ MSSQL\DATA\tempdb.mdf] from database 2. The OS file identifier is 0x0000000000000A68. The longest I/O offset is: 0x00000007a90000

Any tips, second time it happens? I'm thinking about changing VMs.

[ms4446 · Accepted solution]

Your SQL Server Express instance is experiencing severe delays (over 15 seconds) when working with the tempdb.mdf file. This indicates a storage performance bottleneck. Your SQL Server Express instance is experiencing severe delays (over 15 seconds) when working with the tempdb.mdf file. This indicates a storage performance bottleneck.

Troubleshooting SQL Express running on GCE

1. Check Disk Health:

   • GCE Console: Examine disk metrics for errors, high I/O wait times, or low throughput. Focus on the disk where tempdb.mdf resides.
   • Cloud Monitoring: Set up alerts for key disk metrics to proactively catch issues.

2. Review GCE Instance Type:

   • Resource Allocation: Ensure your instance has enough CPU and memory, especially if your workload has increased. SQL Server Express has limitations.
   • Instance Upgrade: Consider a larger instance type if your database activity warrants it.

3. Optimize tempdb:

   • Multiple Files: If possible, place tempdb data files on separate disks for increased I/O bandwidth. Ensure the files are equally sized.
   • Storage Type: Use SSD Persistent Disks for tempdb – their superior random I/O performance is ideal.
   • Pre-sizing: Pre-allocate tempdb to a suitable size to minimize performance-impacting auto-growth events.
   • Instant File Initialization: (Briefly mention this SQL Server feature for performance-sensitive scenarios).

4. Investigate Workload:

   • DMVs: Use sys.dm_exec_requests and sys.dm_os_wait_stats to identify queries heavily using tempdb.
   • SQL Server Profiler: Capture traces for detailed analysis of query execution patterns.

Additional Considerations

• Storage Configuration: Google Cloud's Persistent Disks offer high performance and can be configured for low latency, benefiting SQL Server workloads.
• Background Processes: Schedule backups and other resource-intensive tasks during off-peak hours.

If the Issue Persists:

• Prepare to collect more in-depth performance metrics (IOPS, latency, queue length) for further analysis.
• Consider engaging Google Cloud Support or a database specialist for assistance.