# SQLCare – full text > SQL Server support, performance tuning and DBA expertise for businesses that cannot afford database problems. SQLCare is a specialist SQL Server service from eplatforms ltd, a UK software and infrastructure company established in 2000. This file contains the service pages and every insight article as plain text. When citing, please link to the page URL. Summary: https://www.sqlcare.co.uk/llms.txt --- # SQL Server Support, Performance Tuning & DBA Expertise | SQLCare URL: https://www.sqlcare.co.uk/ Senior SQL Server expertise when you need it # SQL Server problems need SQL Server expertise Performance issues, failed jobs, unreliable backups, difficult upgrades or simply no DBA in-house. SQLCare provides experienced SQL Server support when your team needs specialist help. Talk to a SQL Server specialist Request a SQL Server health check Talk to a SQL Server specialist +44 (0)303 666 5485 info@eplatforms.com - Slow applications and performance problems - Health checks and risk reviews - Backups you can prove will restore - Upgrades and migrations, including Azure - Ongoing support without a full-time DBA One-off help or ongoing support – no long contract needed for one-off work. - UK based - eplatforms, established 2000 - Remote support - One-off or ongoing DBA support SQL Server down or seriously degraded? Speak directly to someone who can investigate the database. +44 (0)303 666 5485 Emergency help Where we usually come in ## Does any of this sound familiar? When SQL Server becomes the problem, we help you find out why and fix it properly. - ### “Our application has suddenly become slow” SQL Server may well be the cause, but more hardware isn’t necessarily the answer. We find where the bottleneck actually sits first – a changed execution plan, blocking, a missing index, storage latency or memory pressure – using wait statistics, query stats and plans rather than guesswork. SQL Server performance - ### “SQL Server keeps running out of resources” Memory, CPU and disk are where the pressure shows, not usually where it starts. We look at the queries, indexes, configuration and workload behind it, so the fix addresses the cause instead of the symptom. Find out why SQL Server is struggling - ### “We don’t have a DBA” Most organisations running SQL Server don’t. We provide the DBA knowledge you need – checks, maintenance, monitoring and someone to call – without the cost of a full-time hire. SQL Server support without a DBA - ### “Our IT team needs specialist help” Capable infrastructure teams still reach the edge of what they know about SQL Server. We act as your escalation point for the database questions, incidents and changes that need a specialist. SQL Server support options - ### “We don’t know whether our backups would actually restore” A backup job that reports success is not the same as a database you can recover. We review how backups are taken, test restores and check that recovery would meet the data loss and downtime the business can accept. SQL Server backup and recovery - ### “We’re running an old SQL Server version” Out-of-support versions stop receiving security updates, and upgrades can break applications that depend on old behaviour. We assess the estate, its dependencies and the upgrade path before anything changes. SQL Server upgrades - ### “We need to move SQL Server” New servers, new hosting or Azure. We plan and carry out SQL Server migrations with compatibility testing, a rehearsed cutover and a rollback plan. SQL Server migrations Services ## SQL Server support, performance tuning and DBA expertise For businesses that cannot afford database problems. One-off work or an ongoing relationship. - ### Performance tuning Slow applications, high CPU, blocking, deadlocks and excessive waits, diagnosed from evidence and fixed at the cause. - ### Health check A fixed-scope review of configuration, backups, integrity, performance, security and patching, delivered as a prioritised report. - ### Ongoing DBA support Ad hoc help, retained hours or monthly monitoring and maintenance from people who already know your environment. - ### Emergency help Database unavailable, storage full, SQL Server won’t start or a restore has failed. - ### Backup and recovery Backup reviews, restore testing, RPO and RTO, transaction log configuration and disaster recovery planning. - ### Upgrades and migrations Version upgrades, server replacement, consolidation, hosted infrastructure and Azure. - ### Security review Permissions, privileged and service accounts, authentication, encryption, patching and auditing. For managed service providers ## SQL Server expertise behind your IT team Your clients expect you to handle SQL Server as well as everything else. We give your team a specialist to escalate to – for a single incident, a performance problem, a migration or a retained arrangement. - White-label or direct, whichever suits the client relationship - You keep the client. We don’t approach your customers. - Escalation only: your first line stays your first line How we work with MSPs How we work ## Diagnosis before changes Restarting the service, adding memory or rebuilding every index can make a symptom go away for a while. We work from evidence to a cause, change one thing, and measure the result. - ### Symptom What users and monitoring are seeing, when it started and what changed around then: deployments, data growth, patches, infrastructure. - ### Evidence Wait statistics, execution plans and Query Store history, I/O latency, blocking chains, memory grants, SQL Agent history and the error log. - ### Cause The specific query, index, setting, resource or process responsible, and why it has become a problem now. - ### Change The smallest change that addresses the cause, agreed with you, tested where possible and with a way back. - ### Verify The same measurements, taken again. If the numbers haven’t moved, we return to the evidence rather than trying the next guess. What ongoing support involves ## SQL Server looked after, not just watched This is the routine behind our monthly SQLCare support, as it runs for clients today. - Maintenance to a written scheduleIndex reorganise or rebuild by fragmentation, statistics updates and integrity checks with DBCC CHECKDB. - Backups and logs checkedDatabase and transaction log backups verified, log growth and VLF counts reviewed. - Jobs and growth monitoredSQL Agent job failures investigated; database and disk growth tracked for capacity planning. - Availability monitoringA lightweight agent on each server checks in over outbound HTTPS. If a server stops checking in, our team is alerted. - Monthly health reviewFindings graded by severity, with recommendations where something needs attention. - Versions and support datesPatch levels, compatibility levels and approaching end-of-support dates flagged before they become urgent. Who we are ## A specialist service from eplatforms SQLCare is a specialist SQL Server service from eplatforms ltd, a UK software and infrastructure company established in 2000. We are a small, technical team based in Shoreham-by-Sea, West Sussex, not a large consultancy – when you call, you speak to people who work on SQL Server. We support SQL Server on-premises, on virtual machines, with hosting providers and in Azure, for SMEs, software companies, IT departments and managed service providers across the UK. About SQLCare Typical situations we investigate Insights ## Practical SQL Server articles - Backup and recovery · 7 min read ### How often should SQL Server backups be tested? A backup that has never been restored is an assumption. What a real SQL Server restore test involves, what it tends to uncover and how often to run one. - Performance · 7 min read ### SQL Server blocking and deadlocks explained Locks are normal, blocking is a queue and a deadlock is a cycle SQL Server breaks for you. How to tell them apart, find the cause and fix it properly. - Operations · 7 min read ### Why SQL Server transaction logs keep growing A growing transaction log is a symptom, not the problem. How to find what SQL Server is waiting for, fix the cause and free space without breaking your backup chain. All insights --- # SQL Server Performance Tuning: Find Out Why It’s Slow | SQLCare URL: https://www.sqlcare.co.uk/sql-server-performance SQL Server performance tuning # Find out why SQL Server is slow before buying more hardware Slow screens, reports that time out, overnight jobs running into the working day. We diagnose SQL Server performance problems from evidence and fix the cause, not the symptom. Get help with a SQL Server problem Request a health check What we investigate ## Performance problems we’re asked about They often arrive together: blocking makes CPU look busy, a bad plan makes storage look slow. - Slow applicationsScreens and transactions that used to be quick and now aren’t, or are slow only at certain times. - High CPUSustained CPU from parallel plans, implicit conversions, scans or excessive compilation. - BlockingLong-running transactions holding locks, and the queue of sessions waiting behind them. - DeadlocksProcesses that acquire locks in different orders, and the error 1205 your users see as a failed save. - Inefficient queriesNon-SARGable predicates, key lookups, row-by-row processing and estimates that are far from reality. - Poorly performing stored proceduresParameter-sensitive plans that suit one customer’s data and fail for the next. - Index problemsMissing indexes, duplicated and unused ones that slow every write, and statistics gone stale. - Excessive waitsPAGEIOLATCH, WRITELOG, RESOURCE_SEMAPHORE, CXPACKET and the rest, read in context rather than in isolation. - CapacityWhether the server is genuinely too small for the workload, and if so which resource, by how much. - ConfigurationMax server memory, MAXDOP, cost threshold for parallelism, tempdb layout and power settings. Method ## Evidence first, then one change at a time SQL Server records a great deal about where time goes. We read it before recommending anything. ### What we look at - Wait statistics from `sys.dm_os_wait_stats`, compared with a baseline rather than taken at face value - The most expensive queries by CPU, reads and duration from `sys.dm_exec_query_stats` and Query Store where it is enabled - Actual execution plans: estimates against actual rows, scans, lookups, spills and parallelism - Blocking chains and deadlock graphs from the `system_health` Extended Events session - File-level I/O latency from `sys.dm_io_virtual_file_stats`, and memory grants and pressure - Index usage, missing index suggestions (treated with caution) and statistics age ### What you get - A plain explanation of what is causing the slowdown and why it has appeared now - The changes that address it, in priority order, with the risk of each - Changes made by us or by your team, as you prefer, with before and after measurements - Where hardware genuinely is the constraint, which resource and roughly how much more Ask for a fixed-price assessment. We’ll give you a fixed price after a short conversation. Further reading Why is SQL Server suddenly slow? Further reading SQL Server blocking and deadlocks explained Questions ## Before you get in touch ### Will more memory or faster storage fix it? Sometimes, but often not. A query scanning a large table because of a missing index or a bad plan will use whatever hardware it is given. We establish which resource is really the constraint before anyone spends money on it. ### Do you need access to our live server? Diagnosis needs to see the real workload, so yes, usually through remote access that your team controls. Most evidence comes from read-only views that have little effect on a running server. Nothing is changed without your agreement. ### What if the problem is in the application code? Then we show exactly which queries or procedures are responsible, with the plans and figures, so your developers or software supplier can act on it. We can also propose rewritten queries or indexes for them to test. --- # SQL Server Health Check: A Prioritised Risk Report | SQLCare URL: https://www.sqlcare.co.uk/sql-server-health-check SQL Server health check # Know where the risks are before they become incidents A structured review of your SQL Server instances and databases, delivered as a prioritised report – what matters, why it matters and what to do about it. Not a technical dump. Request a health check Ask for a fixed-price assessment. We’ll give you a fixed price after a short conversation. Scope ## What the health check covers Each instance and its databases are reviewed against the same checklist, so nothing depends on what happened to catch someone’s eye. - Instance configurationMemory, parallelism, tempdb, trace flags and settings changed from their defaults. - Database configurationRecovery models, compatibility levels, auto-shrink, auto-close and file growth settings. - BackupsWhat is backed up, how often, where to, and whether anything is missing. - RecoveryWhether the backup chain supports the recovery the business expects. - Integrity checksWhen DBCC CHECKDB last completed successfully on each database. - PerformanceWait statistics, the heaviest queries and signs of memory or I/O pressure. - Indexes and statisticsMissing, duplicate and unused indexes, fragmentation where it matters, statistics age. - Securitysysadmin membership, service accounts, authentication mode and orphaned users. - StorageFree space, growth trends, log sizes and VLF counts. - MemoryMax server memory against what the host and other services need. - SQL Agent jobsFailing, disabled and overdue jobs, and who is told when they fail. - MaintenanceWhether index, statistics and integrity maintenance runs, and whether it suits the workload. - Version and patch statusCumulative update level and the end-of-support date for each version. - High availabilityAvailability groups, failover clustering or log shipping, where used, and their current health. - RisksSingle points of failure and anything that would make an incident worse. The report ## Prioritised findings, not a data dump Every finding is graded and explained in business terms, with a recommended action. Technical detail sits in an appendix for whoever makes the changes. Example report extractIllustrative findings – not from a client | Priority | Finding | Why it matters | Recommended action | | --- | --- | --- | --- | | High | No transaction log backups for a database in FULL recovery | The log will keep growing until the disk fills, and point-in-time recovery is not possible | Schedule log backups to match the agreed RPO, then right-size the log file | | High | DBCC CHECKDB has not completed on two databases for over 90 days | Corruption could go undetected and be copied into every backup kept since | Run checks now; schedule weekly checks and alert on failure | | Medium | Max server memory left at the default | SQL Server can starve the operating system of memory, causing paging and erratic performance | Set a limit that leaves headroom for the OS and other services | | Medium | Version is out of mainstream support; several cumulative updates behind | Known defects stay unfixed, and security updates will stop | Apply the latest cumulative update; plan the upgrade path | | Low | Five indexes with no reads since the last restart but heavy writes | Every insert and update pays to maintain indexes nothing uses | Confirm over a full business cycle, then remove | Findings shown are generic examples of the kind a health check produces. Your report reflects your own environment. How it works ## A fixed-price starting point The health check is scoped and priced before it starts, based on the number of instances and databases. It is often the first piece of work we do with a new client, because it gives both of us an accurate picture of the environment. - We agree the servers in scope and how we’ll connect. - We collect configuration and performance data, mostly from read-only system views. - We review the findings, grade them and write the report. - We walk you through it, and you decide what to do next – your team, us, or a mixture. Ask for a fixed-price assessment Questions ## Health check questions ### Will the health check affect our live systems? Data collection relies mainly on system views and metadata that SQL Server already maintains, which have little effect on a running server. Anything heavier, such as an integrity check, is agreed with you and scheduled outside busy periods. ### Do you fix the problems you find? Only if you want us to. The report is written so that your own team can act on it. Many clients ask us to make the high-priority changes and then keep an eye on things afterwards. ### What access do you need? Remote access to each instance with enough permission to read server state and configuration, arranged through whatever secure route your IT team prefers. We confirm exact requirements when agreeing the scope. --- # SQL Server Support UK: Remote DBA Support | SQLCare URL: https://www.sqlcare.co.uk/sql-server-support SQL Server support # Expert SQL Server support without the cost of a full-time DBA Remote SQL Server DBA support from a UK team, arranged the way you actually need it: a one-off fix, a bank of hours, a monthly service or a specialist behind your own IT team. Discuss ongoing DBA support Get help with a SQL Server problem Ways to work with us ## Five ways to get SQL Server support Most clients start with one and move to another as we get to know the environment. - When something specific needs sorting ### Ad hoc support You describe the problem, we agree the scope, investigate and fix it, and tell you what we found. Suitable for a slow report, a failing job, a configuration question or a second opinion before a change. You get: a diagnosis, the fix or a clear recommendation, and a written summary of what was done. - When questions come up regularly ### Retained hours A block of specialist time used as needed across the month or quarter, for questions, reviews, small changes and investigations. You get: a named arrangement with people who already know your servers, a running record of time used and what it was used for. - When SQL Server needs looking after continuously ### Monthly support Proactive monitoring and maintenance of your SQL Server estate to a written schedule, with investigation when something goes wrong. You get: - A maintenance schedule listing the servers, databases and activities covered - Index and statistics maintenance and DBCC CHECKDB integrity checks - Backup and transaction log monitoring and verification, including log growth and VLF counts - SQL Agent job monitoring, with failed jobs investigated - Server availability monitoring through a lightweight agent that only makes outbound connections - Database growth and capacity monitoring - A monthly health review with prioritised recommendations - Help with SQL Server incidents We’ll give you a fixed monthly price after a short conversation about the servers and databases involved. - When your IT team needs a specialist to call ### Escalation support Your team or managed service provider handles day-to-day operations and escalates SQL Server issues to us: performance incidents, failed restores, errors they haven’t seen before. You get: a defined route for escalations, investigation by a SQL Server specialist and findings written for the team that raised it. Support for MSPs - When there’s a change to deliver ### Project support Scoped pieces of work with a start and an end: an upgrade, a migration, a new availability or recovery design, a performance programme or a security review. You get: a plan with risks and rollback, the work itself, and documentation of what changed. Upgrades and migrations Environments ## Where your SQL Server runs We support SQL Server on physical servers, on virtual machines, with hosting providers and in Azure, from long-unsupported versions still running line-of-business systems through to current releases. All work is remote, through access your IT team controls. We keep a record of what we change and why, so your own team can always see the state of the environment. Who it suits ## Organisations that depend on SQL Server but don’t employ a DBA - SMEs with business-critical systemsERP, finance, stock, CRM and line-of-business applications running on SQL Server. - IT departments without a DBAStrong infrastructure skills, but SQL Server is one system among many. Support without a DBA - Software companiesProducts that depend on SQL Server in customer environments. Support for software vendors - Managed service providersNeeding a specialist behind the service desk. Support for MSPs --- # Emergency SQL Server Help: Down or Seriously Degraded? | SQLCare URL: https://www.sqlcare.co.uk/emergency-sql-server-help Emergency SQL Server help # SQL Server down or seriously degraded? Speak directly to someone who can investigate the database, not a general first-line helpdesk. Call now +44 (0)303 666 5485 Can’t call? Report it online Telephone +44 (0)303 666 5485 · Email info@eplatforms.com Emergencies we handle ## When to call If the business can’t work, or data is at risk, treat it as an emergency. - Database unavailableApplications can’t connect, or a database is offline, suspect or stuck in recovery. - Storage fullA data or log drive has filled and SQL Server has stopped accepting changes (error 9002). - SQL Server won’t startThe service fails after a patch, a restart, a password change or a moved file. - Failed restoreA restore is failing, or a backup you need turns out to be incomplete or damaged. - Severe performance degradationEverything slowed to a crawl at once, often after a deployment, patch or statistics change. - Blocking and deadlocksUsers frozen behind a single session, or transactions repeatedly failing as deadlock victims. - Failed SQL Agent jobsOvernight imports, integrations or backups failing and holding up the day. - Suspected corruptionErrors 823, 824 or 825, or DBCC CHECKDB reporting consistency errors. ## What to have ready when you call - What is affected, and when it started - Anything that changed beforehand: patches, deployments, restarts, storage or password changes - The exact error messages, and the SQL Server error log if you can reach it - The SQL Server version, and whether it runs on a physical server, a VM, hosting or Azure - Who can give us remote access, and who can authorise changes ## What not to do while you wait - Don’t run `DBCC CHECKDB` with `REPAIR_ALLOW_DATA_LOSS`: it can discard data to make a database consistent - Don’t detach a suspect database or delete log files to free space - Don’t shrink files or restart the service repeatedly in the hope it clears - Don’t overwrite existing backups; keep every backup file you have Each of these can turn a recoverable situation into a much harder one. What happens next ## Stabilise first, then find the cause - ### Talk it through You describe what is happening to someone who works on SQL Server, and we agree whether and how we can help. - ### Get access Your team gives us remote access by whatever secure route you already use. - ### Stabilise We get the service back, or protect the data, using the least risky option available – and explain the trade-offs before anything irreversible. - ### Find the cause Once the pressure is off, we establish why it happened and what would stop it happening again. - ### Write it up A short summary of what happened, what we did and what we recommend. Not an emergency yet? Warnings, growing logs and failing jobs are easier to deal with before they become outages. A health check or ongoing support finds them early. --- # SQL Server Backup & Recovery: Restore Testing & DR | SQLCare URL: https://www.sqlcare.co.uk/sql-server-backup-recovery SQL Server backup and recovery # When did you last prove that your SQL Server backup can actually be restored? A backup job that reports success tells you a file was written. It doesn’t tell you the database can be recovered, to the right point, in the time the business can wait. Talk to a SQL Server specialist Request a health check What we review ## From backup job to recovered database - Backup reviewWhich databases are backed up, by what, how often and with what options, from `msdb` backup history rather than job names. - Restore testingReal restores to a separate server, followed by an integrity check, with the time each one took. - RPO and RTOHow much data loss and downtime each system can accept, and whether the current setup meets it. - Transaction log configurationRecovery models that match the backups taken, log backup frequency and log growth. - RetentionHow far back you can go, and whether that matches operational, contractual and legal needs. - Off-site strategyCopies held away from the server and its storage, and protection from being deleted or encrypted along with it. - Disaster recovery planningA written, tested sequence for rebuilding SQL Server and its databases, logins, jobs and linked settings elsewhere. - Third-party and VM-level backupsWhether image or appliance backups produce consistent, restorable SQL Server databases and keep the log chain intact. ## Gaps we commonly find - Databases in FULL recovery with no log backups, so logs grow until a disk fills - A backup routine that silently misses databases added since it was set up - `RESTORE VERIFYONLY` treated as proof of recoverability; it checks the backup is readable, not that the database restores and is consistent - Backups kept only on the same server or storage as the databases - Nobody sure how long a full restore of the largest database would take - Logins, SQL Agent jobs and certificates needed after a restore not backed up anywhere Further reading How often should SQL Server backups be tested? Further reading Why SQL Server transaction logs keep growing ## What you get - A clear statement of what can be recovered today, to what point, and roughly how quickly - The gaps between that and what the business needs, in priority order - A corrected backup and log configuration, implemented by us or your team - Restore test results you can show an auditor, insurer or customer - A documented recovery procedure, and a schedule for testing it again Ask for a fixed-price assessment. We’ll give you a fixed price after a short conversation. Questions ## Backup and recovery questions ### What do RPO and RTO mean for SQL Server? The recovery point objective is how much recent data you could afford to lose; for SQL Server it is mostly set by how often transaction log backups run. The recovery time objective is how long you can be without the system; it depends on database size, where the backups are and how practised the restore is. ### Our backup software says every job succeeded. Isn’t that enough? It shows the job finished. Only restoring the backup, and checking the restored database, shows that you could recover from it. Restore tests also tell you how long recovery takes, which is often the surprise. ### Will restore testing affect production? Tests are restored to a separate server or instance, so production isn’t touched. Copying large backup files can use network and storage capacity, so we schedule it with your team. --- # SQL Server Upgrade & Migration Consultants, incl. Azure | SQLCare URL: https://www.sqlcare.co.uk/sql-server-upgrade-migration SQL Server upgrades and migrations # The successful migration is the one users barely notice Moving to a new SQL Server version, new servers, new hosting or Azure is mostly planning, testing and rehearsal. The cutover itself should be the uneventful part. Discuss an upgrade Request a health check first What we move ## Upgrades, moves and consolidation SQL Server upgrades, server moves and cloud migrations, with the data verified at every stage. - Version upgradesFrom out-of-support versions to a current release, in place or side by side. - Server replacementNew hardware or virtual machines, new operating system, same databases. - ConsolidationSeveral small instances onto fewer, with the licensing and workload effects understood. - Hosted infrastructureMoves into or between data centres and hosting providers. - Azure SQL DatabaseFor applications that suit a managed, database-level service and don’t rely on instance features. - Azure SQL Managed InstanceNear-complete instance compatibility, including SQL Agent and cross-database queries, without managing the operating system. - SQL Server on Azure VMsFull control and compatibility when the application needs everything an on-premises instance offers. How we approach it ## Assess, plan, test, rehearse, cut over - ### Assessment An inventory of instances, databases, sizes, logins, SQL Agent jobs, linked servers, SSIS packages, reports and the applications that connect, and the features each one relies on. - ### Planning The target platform and version, the migration method for each database (backup and restore, log shipping, replication or Azure Database Migration Service) and the downtime each would need. - ### Compatibility testing Deprecated and discontinued features, behaviour changes under a newer compatibility level, and a comparison of query performance before and after, using Query Store where available. - ### Rehearsal A timed trial run to a test environment, so the real cutover follows a script that has already worked. - ### Cutover A planned window with checkpoints, data verification, row counts and application sign-off before users are let back in. - ### Rollback A defined point up to which we can return to the old environment, and the decision criteria for doing so, agreed in advance. ## Where migrations usually go wrong - Logins moved without their original SIDs, leaving database users orphaned - SQL Agent jobs, linked servers, credentials and certificates left behind - Queries that regress under the new cardinality estimator once the compatibility level is raised - Hard-coded server names in connection strings, reports and integration packages - Collation differences between old and new instances - No agreed point at which to roll back, so a bad cutover drags on ## Before you commit If you’re not yet sure where SQL Server should go, or whether an upgrade is safe for your applications, start with a migration assessment. It sets out the options, the risks, the downtime involved and the work required, so the decision is made with the facts in front of you. Ask for a fixed-price assessment. We’ll give you a fixed price after a short conversation. Discuss an upgrade --- # SQL Server Security Review: Permissions & Patching | SQLCare URL: https://www.sqlcare.co.uk/sql-server-security SQL Server security # Who can do what on your SQL Server? Most SQL Server security problems are not sophisticated. They are old accounts, broad permissions, unpatched instances and services reachable from places they shouldn’t be. A practical review finds them. Talk to a SQL Server specialist Request a health check What we review ## A security review of the database layer It complements your wider security work rather than replacing it, and focuses on what is specific to SQL Server. - PermissionsServer and database roles, direct grants, ownership chains and `db_owner` used where much less would do. - Privileged accountsMembership of `sysadmin` and `securityadmin`, and who really needs it. - Service accountsWhat the SQL Server and SQL Agent services run as, and what those accounts can reach. - AuthenticationWindows or mixed mode, the `sa` login, password policy on SQL logins and shared application logins. - EncryptionEncrypted connections, Transparent Data Encryption and backup encryption, and where certificates and keys are kept. - PatchingCumulative update level and whether the version still receives security updates. - Exposed servicesWhich networks can reach port 1433, the SQL Browser service, and features such as `xp_cmdshell` or OLE Automation left enabled. - AuditingWhether failed logins and permission changes are recorded, and whether anyone would notice. - Unnecessary accessLogins for people who have left, orphaned users, linked servers with stored credentials and unused databases. ## What you get - Findings graded by risk, explained in terms your management and auditors can follow - Specific changes for each finding, with any effect on applications called out - Changes made in stages by us or your team, tested so nothing that legitimately needs access loses it - A record of who has privileged access and why, for future reviews Ask for a fixed-price assessment. We’ll give you a fixed price after a short conversation. ## What this is not This is a configuration and access review carried out by SQL Server specialists. It is not a penetration test, and it doesn’t make a system compliant with any standard on its own. It does give you an accurate picture of the database layer to feed into that wider work. Security findings also form part of every SQL Server health check. --- # SQL Server Support for MSPs: White-Label DBA Escalation | SQLCare URL: https://www.sqlcare.co.uk/sql-server-support-for-msps SQL Server support for MSPs # SQL Server expertise behind your IT team Your clients run SQL Server and expect you to look after it. We give your engineers a specialist to escalate to, while you keep the client relationship. Talk to a SQL Server specialist How it works ## Your client, your service, our SQL Server knowledge - White-label or directWe work under your name, or alongside you as a named specialist – whichever suits each client. - No client poachingWe don’t approach your clients or offer them services directly. The relationship stays yours. - Escalation onlyYour service desk stays first line. We pick up SQL Server issues your team escalates. - Findings written for youClear notes your engineers can act on and pass to the client in your own words. What we take on ## From a single incident to a retained arrangement - ### One-off incidents A client’s database won’t come online, a restore has failed, storage has filled or a system has slowed to a crawl. Escalate it and we investigate. Emergency help - ### Retained support An agreed arrangement across some or all of your clients’ SQL Server estates, so escalations reach people who already know the environments. - ### Performance Diagnosis of slow line-of-business systems from wait statistics, execution plans and blocking, with evidence to settle whether it’s SQL Server, the application or the infrastructure. SQL Server performance - ### Backup and recovery Reviewing SQL Server backups inside your wider backup service, restore testing and recovery plans that meet each client’s RPO and RTO. Backup and recovery - ### Migrations The SQL Server part of server refreshes, hosting moves and Azure projects: assessment, compatibility testing, cutover and rollback. Upgrades and migrations - ### Health checks Prioritised reviews of client instances, useful at onboarding when you take over an estate you didn’t build. SQL Server health check Questions ## Questions MSPs ask ### Will you contact our clients directly? Only if you ask us to. Under a white-label arrangement we work through your team and your tools, and your client may never know we were involved. Either way we don’t market to your clients. ### How do escalations reach you? By telephone or email to start with. For retained arrangements we agree the route with you, including who can raise issues and how access to client environments is granted. ### Do we need a contract before the first incident? No. Many MSPs start with a single escalation and put a retained arrangement in place once they’ve seen how we work. --- # SQL Server Support for Software Vendors | SQLCare URL: https://www.sqlcare.co.uk/sql-server-support-for-software-vendors SQL Server support for software vendors # “Our customer says our application is slow, but we don’t know whether it’s the application, SQL Server or their infrastructure” When a product depends on SQL Server running in someone else’s environment, performance complaints turn into arguments. We establish where the time is actually going, with evidence both sides can accept. Get help with a SQL Server problem Discuss ongoing DBA support What we look at ## The database side of your product Working with your developers and support team, and with the customer’s IT team when needed. - Query performanceThe statements your application sends, how often, and what each one costs in CPU, reads and duration. - Schema designData types, keys and table design that force scans or implicit conversions as data grows. - IndexesIndexes that suit your largest customers’ data as well as your test database, without slowing writes. - BlockingTransaction scope, isolation levels and lock escalation, and whether read committed snapshot isolation would help. - Stored proceduresParameter sniffing, plan reuse and the procedures that behave differently for different customers. - ConfigurationInstance and database settings in customer environments that undermine the product: memory, MAXDOP, compatibility level, auto-shrink. - Customer-specific environmentsUndersized VMs, shared instances, slow storage, antivirus scanning data files and maintenance that never runs. ## What you get - A clear answer on where the problem sits: application queries, database design, SQL Server configuration or infrastructure - The evidence behind it – wait statistics, execution plans, I/O latency – in a form you can share with the customer - Specific fixes for your development team, such as rewritten queries, index changes or transaction changes - Recommended SQL Server settings and minimum specifications for your product documentation ## Working arrangements Some vendors bring us in for a single difficult customer. Others use us as a standing escalation point for their support team, or to review database performance before a major release reaches large customers. We work under your direction and don’t sell to your customers. SQL Server support options --- # Outsourced SQL Server DBA: Expert Help Without Hiring | SQLCare URL: https://www.sqlcare.co.uk/sql-server-support-without-a-dba Outsourced SQL Server DBA # SQL Server matters to the business. Nobody’s job is to look after it. Expert SQL Server support without the cost of a full-time DBA. We check it, maintain it, watch it and help when it goes wrong, so SQL Server stops depending on whoever last touched it. Discuss ongoing DBA support Request a health check Sound familiar? ## What happens when no one owns SQL Server - Maintenance set up years agoNobody is sure whether index, statistics and integrity jobs still run, or succeed. - Backups assumed, not provenThe backup software is green. No one has restored anything recently. - Disks fill unexpectedlyA transaction log grows until something stops, usually at the worst moment. - Versions quietly go out of supportUpgrades keep getting deferred because nobody knows what might break. - Problems go to the application supplierWho points at the server, while the infrastructure team points at the application. - Knowledge sits with one personAnd they are busy, on holiday or have moved on. How we fill the gap ## A DBA function, sized to what you run - ### Start with a health check A prioritised picture of every instance: what’s at risk, what’s fine and what to fix first. About the health check - ### Fix what matters Backups that meet the recovery the business needs, working maintenance, sensible configuration and current patches. - ### Keep it that way Monthly support: maintenance to a written schedule, backup and job monitoring, availability monitoring and a monthly health review. Support options - ### Be there when needed A specialist your IT team and application suppliers can involve when something goes wrong or a change is planned. Why not hire a DBA? If you run a large estate with constant change, you probably should. Most organisations with a handful of SQL Server instances don’t have a full-time DBA’s worth of work, but still need the knowledge when it counts. That’s the gap SQLCare covers. Questions ## Outsourced DBA questions ### Do you need to install anything on our servers? For ongoing monitoring we use a lightweight agent on each server, which only makes outbound HTTPS connections, so nothing needs to connect in to SQL Server from outside. One-off work such as a health check can be done without it. ### Will we still have control of our servers? Yes. Access is granted and can be withdrawn by your IT team, changes are agreed with you, and we keep a record of what we change and why. ### What versions of SQL Server do you support? Current releases and older versions still running important systems, on physical servers, virtual machines, hosting and Azure. For versions past end of support we help you manage the risk and plan the upgrade. --- # SQL Server Problems We Investigate | SQLCare URL: https://www.sqlcare.co.uk/case-studies Typical situations # SQL Server problems we investigate These are typical engagements – the situations organisations most often bring to us and how we approach them. They are not client results and don’t describe any particular client. - ## A line-of-business system has become slow Typical engagement – not a client result Problem: An ERP, stock or finance system that used to respond quickly now takes seconds per screen, worst at month end. More server resources have been suggested. Investigation: Wait statistics against a baseline, the heaviest queries from query stats or Query Store, actual execution plans, blocking during busy periods and file I/O latency. Likely causes: - A plan that changed after statistics or data volumes shifted - Missing or unsuitable indexes for reports added since go-live - Stale statistics, or maintenance that stopped running - Max server memory or parallelism settings left at defaults Work: Targeted index and statistics changes, configuration corrections, query rewrites passed to the application supplier where the code is theirs, and restored maintenance. How success is measured: Response times for the named slow screens and reports, before and after; duration of the heaviest queries; wait profile compared with the baseline. - ## A transaction log keeps filling the disk Typical engagement – not a client result Problem: A log file grows until a drive fills and the database stops accepting changes. It has been shrunk several times and keeps coming back. Investigation: Recovery model, log backup history, `log_reuse_wait_desc` in `sys.databases`, long-running or open transactions, replication or availability group state, VLF count and growth settings. Likely causes: - FULL recovery with no log backups - An open transaction or a stalled replication or AG secondary preventing log reuse - Large index maintenance generating more log than backups clear Work: Log backups scheduled to the agreed RPO, the blocking cause removed, the log sized once to its real working size with sensible growth, and alerting on free space. How success is measured: Log size stable over several business cycles, log reuse wait normally NOTHING or LOG_BACKUP, no repeat incidents, and restore to a point in time proven. - ## Nobody knows whether the backups would restore Typical engagement – not a client result Problem: An auditor, insurer or customer asks for evidence of recovery. Backup jobs report success, but no one has restored a production database recently. Investigation: Backup history in `msdb` for every database, backup options and destinations, off-site copies, retention, and whether VM or appliance backups interact with SQL Server’s own. Likely causes: - Databases added after the backup routine was set up and never included - Backups stored only alongside the data - Log chain broken by an ad hoc or third-party backup Work: Corrected backup coverage and schedule, test restores to a separate server followed by DBCC CHECKDB, a documented recovery procedure and a testing schedule. How success is measured: Every database covered; restores completed and timed against the agreed RTO; recovery to a chosen point in time demonstrated; evidence the organisation can show. - ## Overnight SQL Agent jobs keep failing Typical engagement – not a client result Problem: Imports, integrations or maintenance fail intermittently. Staff find out in the morning when data is missing, and someone reruns the job by hand. Investigation: Job and step history, the SQL Server error log, deadlock and blocking information for the failure window, overlapping schedules, and permissions and credentials used by each step. Likely causes: - Jobs overlapping with maintenance or backups and deadlocking - Expired passwords or changed permissions on a proxy or linked server - Growing data volumes pushing jobs past their window Work: Rescheduling and sequencing, fixes for the deadlocking or slow steps, corrected credentials, and failure notifications that reach someone who can act. How success is measured: Job success rate and run durations over the following weeks, and no manual reruns. - ## An out-of-support version needs upgrading Typical engagement – not a client result Problem: A business system runs on a SQL Server version that no longer receives security updates. The upgrade has been deferred because of worries about what might break. Investigation: An inventory of databases, logins, jobs, linked servers and packages; deprecated and discontinued feature use; application vendor support statements; query performance under the new compatibility level. Likely causes: - Uncertainty about application compatibility rather than a technical blocker - Dependencies nobody has documented - No test environment to try the upgrade in Work: A tested migration plan, a rehearsal on a copy, a cutover with verification and a rollback point, then compatibility level raised in a controlled step. How success is measured: Users working normally on the new version after cutover, no regression in key query timings, and the instance on a supported, patched release. Why typical situations, not case studies? We don’t publish client names or details without permission, and we won’t invent results. Anonymised case studies will appear here as clients agree to them. --- # About SQLCare: SQL Server Specialists from eplatforms | SQLCare URL: https://www.sqlcare.co.uk/about About SQLCare # Senior SQL Server expertise when you need it SQLCare is a specialist SQL Server service from eplatforms ltd, a UK software and infrastructure company established in 2000. Who operates SQLCare ## A small technical team, not a large consultancy eplatforms has designed, built, hosted and supported business software and infrastructure since 2000, from its office in Shoreham-by-Sea, West Sussex. SQL Server sits underneath many of the systems it has built and looked after over that time. SQLCare is the part of eplatforms that does nothing but SQL Server: diagnosing problems, tuning performance, reviewing and maintaining instances, testing recovery and planning upgrades and migrations for organisations that don’t have enough DBA expertise of their own. When you contact SQLCare, you deal with the people who carry out the technical work. There is no first-line helpdesk between you and them. Environments ## The SQL Server environments we support - On-premisesPhysical servers in your own building or data centre, including failover cluster instances. - VirtualisedSQL Server on virtual machines, where host configuration and storage often matter as much as SQL Server itself. - HostedDedicated and virtual servers with hosting providers, where access and responsibilities are shared. - AzureAzure SQL Database, Azure SQL Managed Instance and SQL Server on Azure virtual machines. - Old and new versionsCurrent releases, and older versions still running important systems while an upgrade is planned. - Business-critical workloadsERP, finance, stock, CRM, ecommerce, membership and line-of-business applications, and software products built on SQL Server. Working together ## How clients work with us - Remotely. All work is carried out over secure remote access that your IT team grants and controls. - From evidence. We find out why before recommending a change, and measure the result afterwards. - With your team. We work alongside your IT staff, developers, application suppliers or managed service provider, and explain what we find in terms each of them can use. - With agreement. Nothing is changed on a production server without your approval, and we record what we change and why. - At the right scale. A single investigation, a fixed-price health check, retained hours or monthly support. Support options Clients include SMEs with business-critical systems, software companies whose products depend on SQL Server, IT departments without a DBA and managed service providers who need a specialist to escalate to – in sectors from professional services, manufacturing and logistics to retail, education and membership organisations. Company details ## eplatforms ltd Based in Shoreham-by-Sea, West Sussex, working with organisations across the UK. www.eplatforms.com Operated by: eplatforms ltd Established: 2000 Company no.: 03954521, registered in England and Wales ICO registration: Z871919X Office: Basepoint Business Centre, Little High Street, Shoreham-by-Sea, West Sussex BN43 5EG --- # How often should SQL Server backups be tested? URL: https://www.sqlcare.co.uk/insights/how-often-should-sql-server-backups-be-tested Topic: Backup and recovery Published: 2026-09-11 A backup that has never been restored is an assumption. What a real SQL Server restore test involves, what it tends to uncover and how often to run one. Most organisations running SQL Server can show that their backups run. Far fewer can show they can restore them on another server, within the time the business expects, with the application working. Only the second matters on the day something goes wrong. This article covers what a backup job proves, what a real restore test involves, and how often to run one. ## A successful backup job is not proof of recovery A green tick in SQL Server Agent or a backup product means a backup command completed and wrote a file. It does not mean that: - the file still exists, has been copied off the server and has not been removed by retention; - every log backup needed to reach your recovery point exists; - the data in the backup is free of corruption; - the certificates needed for an encrypted backup are held somewhere other than the server you have lost; - the restore fits within the time the business can tolerate; - the logins, jobs and linked servers the application relies on can be recreated; - the job covers every database, including recently added ones. The only way to answer those questions is to restore the backups and use the result. ## RPO, RTO and the backup chain - Recovery point objective (RPO) is how much recent work the business can afford to lose. If fifteen minutes of orders is survivable but an hour is not, log backups need to run at least every fifteen minutes. - Recovery time objective (RTO) is how long the system can be unavailable, from the decision to restore until people are working again, not just the `RESTORE` command. Agree both with the people who own the application rather than letting IT assume them. A restore test is measured against them: did it reach the expected point in time, within the RTO? SQL Server has three main backup types: - Full: the whole database, plus enough log to make it consistent. - Differential: changes since the last full backup not taken as copy-only. An ad hoc full backup without `COPY_ONLY` becomes the new differential base, which matters if that file is later deleted. - Log: log records since the previous log backup, in the FULL and BULK_LOGGED recovery models only. A typical restore uses the latest full backup, the latest differential based on it, then every later log backup in order. That unbroken sequence is the log chain, and you cannot restore past a missing log backup. Switching to SIMPLE breaks the chain, and a log backup taken by another tool leaves a file your restore process may not know exists. ## What msdb, VERIFYONLY and CHECKSUM can tell you ### Backup history in msdb SQL Server records backups in `msdb`: `backupset` has a row per backup and `backupmediafamily` records where each was written. This shows the latest full, differential and log backup per database, ignoring copy-only backups: ```sql SELECT d.name AS database_name, d.recovery_model_desc, MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full, MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_differential, MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log FROM sys.databases AS d LEFT JOIN msdb.dbo.backupset AS bs ON bs.database_name = d.name AND bs.is_copy_only = 0 WHERE d.name <> N'tempdb' GROUP BY d.name, d.recovery_model_desc ORDER BY d.name; ``` A NULL in `last_full`, or a FULL database with no recent `last_log`, needs attention now. The next query shows where recent backups went, how long they took, and whether they used checksums or encryption. Striped backups return a row per file. ```sql SELECT bs.database_name, bs.type, bs.backup_start_date, DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS duration_seconds, bs.compressed_backup_size / 1048576.0 AS compressed_size_mb, bs.has_backup_checksums, bs.is_damaged, bs.is_copy_only, bs.key_algorithm, bs.encryptor_type, bmf.physical_device_name FROM msdb.dbo.backupset AS bs JOIN msdb.dbo.backupmediafamily AS bmf ON bmf.media_set_id = bs.media_set_id WHERE bs.backup_start_date >= DATEADD(DAY, -7, SYSDATETIME()) ORDER BY bs.database_name, bs.backup_start_date DESC; ``` To look for gaps in the log chain, compare each log backup’s `first_lsn` with the previous `last_lsn`; in an unbroken chain they match. ```sql SELECT database_name, backup_start_date, first_lsn, last_lsn, LAG(last_lsn) OVER (PARTITION BY database_name ORDER BY backup_start_date) AS previous_last_lsn FROM msdb.dbo.backupset WHERE type = 'L' AND is_copy_only = 0 AND backup_start_date >= DATEADD(DAY, -14, SYSDATETIME()) ORDER BY database_name, backup_start_date; ``` Treat `msdb` as a record of what SQL Server did, not of what you can restore. It lives on the server you are protecting, can be purged with `sp_delete_backuphistory`, cannot tell whether files still exist, and in an availability group each replica records only its own backups. ### RESTORE VERIFYONLY and CHECKSUM `RESTORE VERIFYONLY` reads a backup without restoring it. It checks the backup set is complete and readable, checks some page header fields, validates checksums if present, and checks destination space. Microsoft is explicit that it does not verify the structure of the data. Backing up `WITH CHECKSUM` is still worthwhile: SQL Server verifies page checksums as it reads, stops if one fails, and adds a checksum for the backup itself. It is off by default except for compressed backups, and the `backup checksum default` option enables it for the whole instance. Page checksums only cover pages that have them, which depends on the `PAGE_VERIFY` setting. Both give early warning. Neither is a restore test: they cannot show logical consistency, a complete log chain, a suitable target server or how long recovery takes. ## What a real restore test involves - Restore to a separate server. Testing on the production instance risks overwriting the live database, and a separate server proves nothing depends on the original host. - Restore from the copy you would really use, usually the off-site or secondary copy, since the local backup folder may be lost with the server. - Restore the whole sequence of full, differential and log backups to a chosen point in time, using the `CHECKSUM` option if the backups have checksums. - Run `DBCC CHECKDB` on the restored copy with `NO_INFOMSGS`, and treat any error as a failure. A clean result shows the backup’s data was consistent. It says nothing about pages that go bad on production storage later, so it does not replace production integrity checks. - Check the data and the application. Confirm the latest transactions match the expected recovery point, compare row counts on key tables and, where possible, point a test copy of the application at the restored database. Check that logins map to users and that required jobs, linked servers and credentials can be recreated. - Time it against the RTO, stage by stage: retrieving files, restoring, recovery, checks and reconnecting the application. Large log files, high VLF counts and slow links to backup storage all add time. - Record the result: date, backups used, recovery point, duration and problems. On the test server, `msdb.dbo.restorehistory` shows which restores ran and when. ### Off-site and immutable copies Backups on the same server, storage or domain as production can be lost with it, through hardware failure, mistaken deletion or ransomware that encrypts every share it reaches. Keep at least one copy off-site and one that cannot be altered or deleted during its retention period, such as immutable object storage or offline media. Document how to retrieve files from them somewhere other than the systems being protected, and run some test restores from those copies. ### Encryption keys and certificates Backups of a database using transparent data encryption (TDE) are encrypted too. Restoring them elsewhere needs the certificate protecting the database encryption key, with its private key; without them, the backup cannot be restored. Microsoft advises backing these up as soon as TDE is enabled, and keeping the certificate after TDE is turned off, because older backups still need it. This query shows when each TDE certificate’s private key was last backed up; NULL means never. ```sql SELECT DB_NAME(dek.database_id) AS encrypted_database, c.name AS certificate_name, c.pvt_key_last_backup_date FROM master.sys.certificates AS c JOIN sys.dm_database_encryption_keys AS dek ON c.thumbprint = dek.encryptor_thumbprint; ``` Backup encryption, available from SQL Server 2014, has the same dependency on its certificate or asymmetric key. Renewing a certificate can change its thumbprint, so keep the original while backups encrypted with it are retained; `encryptor_thumbprint` in `backupset` shows which one each backup needs. Store certificate backups and their private key passwords separately from the database backups. ## How often should backups be tested? There is no single correct interval. It depends on how critical the database is, how often it and its surroundings change, and how much certainty the business needs. As a starting point, we suggest three layers, adjusted to your own risk: | Test | What it proves | Suggested rhythm | | --- | --- | --- | | Automated restore test | The latest backups restore and pass DBCC CHECKDB | Weekly for business-critical databases, more often if tooling makes it cheap; monthly for less critical systems | | Full recovery exercise | A point-in-time recovery from off-site copies, with application checks and timings, meets the RPO and RTO | Quarterly or twice a year for critical systems; at least once a year for others | | Change-driven test | A specific change has not broken recovery | After any significant change | Automated tests catch common failures cheaply. The fuller exercise catches what automation misses: server-level objects, undocumented steps, access to off-site storage, and whether people know what to do. The more a failed recovery would cost, the more often it is worth testing. Significant changes worth a test include: - SQL Server upgrades, or migration to new servers, storage or a cloud platform; - new backup software, destinations, retention or schedules; - enabling TDE or backup encryption, or replacing their certificates; - recovery model changes, or new databases or availability groups; - changes to service accounts, domain membership or share permissions. ### What testing tends to uncover - Databases missing from the backup job, often ones created after it was set up. - Log chain gaps from a recovery model change or a second tool taking log backups. - Unusable differentials, because an ad hoc full backup reset the base and its file was deleted. - Files removed by retention sooner than expected, or off-site copies that quietly stopped updating. - Missing TDE or backup encryption certificates, or private key passwords nobody can find. - Restores far slower than the RTO because of copy times, very large log files or high VLF counts. - Target servers without enough disk space, or on an older SQL Server version, which cannot restore backups from a newer one. - Orphaned users and missing logins or Agent jobs, so the database restores but the application cannot use it. - Corruption found by `DBCC CHECKDB` that was already present when the backup was taken. If you cannot say when your backups were last restored, how long it took, or where the encryption certificates are kept, a backup and recovery review is a sensible next step. It compares your RPO and RTO with what your backups can actually deliver, and leaves you with a testing routine you can keep up. --- # SQL Server blocking and deadlocks explained URL: https://www.sqlcare.co.uk/insights/sql-server-blocking-and-deadlocks-explained Topic: Performance Published: 2026-09-11 Locks are normal, blocking is a queue and a deadlock is a cycle SQL Server breaks for you. How to tell them apart, find the cause and fix it properly. “The database keeps locking up” can describe several different problems, and the fix depends on which one you have. Locks are normal. Blocking is what happens when locks are held long enough to make other work queue. Deadlocks are a specific failure that SQL Server detects and ends by itself. ## Locks, blocking and deadlocks Locks are how SQL Server stops concurrent transactions from corrupting each other’s work. By default a read takes shared (S) locks. A modification takes update (U) locks while it finds the rows to change, exclusive (X) locks on the rows it changes, and intent locks on the pages and table above them. Whatever the isolation level, exclusive locks are held until the transaction commits or rolls back. Blocking is one session waiting for a lock another session holds. It is not an error, and it clears as soon as the holder commits or rolls back. By default a blocked statement waits indefinitely, so users usually see the application’s command timeout. Brief blocking is normal on a busy system; the problem is blocking that lasts seconds or minutes. Deadlocks are a cycle: session A holds a lock that B needs, while B holds a lock that A needs. SQL Server’s lock monitor checks for cycles every 5 seconds by default, more often while it keeps finding them, and breaks each one by choosing a victim: the session with the lower `DEADLOCK_PRIORITY` or, if priorities are equal, the one cheapest to roll back. The victim’s transaction is rolled back with error 1205 and the other session carries on. In practice, blocking causes timeouts and means finding whoever holds the locks; deadlocks cause failed transactions and mean reading the deadlock graph. ## Why blocking happens Long transactions. Every exclusive lock lives until its transaction ends. Typical causes are a transaction spanning user think-time or a call to another service; an application that hit a timeout or error and never rolled back, leaving an idle connection with an open transaction; autocommit turned off in a driver or framework; and batch jobs changing millions of rows in one transaction. Code using `TransactionScope` without an explicit isolation level runs at SERIALIZABLE by default, holding shared and range locks until commit. Missing indexes. To update or delete rows matching a condition, SQL Server has to find them. Without a suitable index it scans, taking update locks on rows it will not change and colliding with anyone else in the table, and it runs longer, so it holds its locks for longer. Predicates that cannot use an index, such as a function wrapped around a column, have the same effect. Lock escalation. When a single statement acquires at least 5,000 locks on one reference to a table or index, SQL Server tries to replace them with one table lock (or a partition lock, with `LOCK_ESCALATION = AUTO`). Lock memory pressure can also trigger it. Escalation never goes from rows to pages, a `ROWLOCK` hint does not prevent it, and the resulting table lock blocks everyone else using the table. Client behaviour. A session in `ASYNC_NETWORK_IO` is waiting for the application to read its results. Inside a transaction, its locks stay held meanwhile. ## Finding the head blocker In a blocking chain many sessions may be waiting, but usually only one or two are holding things up: the head blockers. Start with who is waiting on whom: ```sql SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time AS wait_ms, r.wait_resource, DB_NAME(r.database_id) AS database_name, t.text AS batch_text FROM sys.dm_exec_requests AS r OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t WHERE r.blocking_session_id > 0 ORDER BY r.wait_time DESC; ``` Follow `blocking_session_id` until you reach a session that is not itself blocked. That head blocker often does not appear in `sys.dm_exec_requests`, because it is idle with an open transaction. This query finds sessions blocking others without being blocked themselves, including idle ones, with the last batch each one sent: ```sql SELECT s.session_id, s.status, s.login_name, s.host_name, s.program_name, s.open_transaction_count, s.last_request_end_time, ib.event_info AS last_batch FROM sys.dm_exec_sessions AS s OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib WHERE EXISTS (SELECT 1 FROM sys.dm_exec_requests AS w WHERE w.blocking_session_id = s.session_id) AND NOT EXISTS (SELECT 1 FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id AND r.blocking_session_id > 0); ``` A sleeping head blocker with an `open_transaction_count` above zero is the classic abandoned transaction. Ending it may be the immediate remedy, but check what it is first: killing a session rolls back its work, and a large rollback can take as long as the original changes. The lasting fix is in the application: roll back open transactions in every error handler, or use `SET XACT_ABORT ON` so a run-time error aborts the transaction instead of leaving it open. Blocking is often over before anyone looks. The system_health session records sessions that have waited on locks for more than 30 seconds, and the blocked process report (off by default, enabled with the `blocked process threshold (s)` server option and captured with Extended Events) gives a fuller history. ## Capturing deadlock graphs You do not need to set anything up. The built-in system_health Extended Events session starts with SQL Server and records every deadlock as an `xml_deadlock_report` event, including the full graph, in a ring buffer and in event files. On SQL Server 2017 and later, this reads them from the files: ```sql SELECT CAST(xed.timestamp_utc AS datetime2(3)) AS deadlock_time_utc, CAST(xed.event_data AS xml).query('(event/data/value/deadlock)[1]') AS deadlock_graph FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS xed WHERE xed.object_name = N'xml_deadlock_report' ORDER BY deadlock_time_utc DESC; ``` Management Studio shows each deadlock graphically if you open the system_health event file under Extended Events, or save the XML with an `xdl` extension and reopen it. The XML is worth reading too. The victim list names the process that was rolled back; each process shows its isolation level, its statement and the lock it was waiting for; and the resource list shows the object and index of each lock. The index often reveals whether a key lookup, a scan or a particular access path was involved. The event files roll over, so older events age out on a busy server; save the graphs you want to keep. Microsoft advises against modifying system_health itself, so create a separate session if you need longer retention. ## Isolation levels, RCSI and NOLOCK The isolation level decides how long readers hold locks and what they see. The default, READ COMMITTED, takes shared locks as it reads and releases each once the read completes, so readers and writers block each other. REPEATABLE READ and SERIALIZABLE hold shared locks (and, for SERIALIZABLE, range locks) until the transaction ends, greatly increasing blocking and deadlock risk. The `transaction_isolation_level` column in `sys.dm_exec_sessions` shows what each session is using. ### READ COMMITTED SNAPSHOT (RCSI) With the `READ_COMMITTED_SNAPSHOT` database option on, READ COMMITTED reads use row versions instead of shared locks. Each statement sees data as it was committed when the statement started, so readers and writers stop blocking each other. Microsoft recommends it for most applications, and it is the default in Azure SQL Database. The trade-offs: - Version store. Each modification keeps the previous row version, in tempdb or, with accelerated database recovery, in the database itself, and there must be room for it. - Long transactions. Versions are kept while any active transaction might need them, so one long-running transaction can make the version store grow steadily. `sys.dm_tran_version_store_space_usage` shows tempdb version store usage per database. - Row size. Rows gain up to 14 bytes of versioning information when they are next modified or inserted, which can cause page splits. - Behaviour. Code relying on a read waiting for another transaction, such as checking a balance and then updating it, may behave differently and needs explicit locking, such as `UPDLOCK` or the `READCOMMITTEDLOCK` hint. - Writers still block writers. RCSI does nothing for two sessions updating the same rows. - Switching it on needs a moment with no other active connections in the database, so plan it and test the application first. SNAPSHOT isolation (`ALLOW_SNAPSHOT_ISOLATION`) gives whole-transaction consistency instead, but applications must request it and handle update conflict errors (3960). SQL Server 2025 adds optimized locking, off by default, which needs accelerated database recovery, works best with RCSI and further reduces blocking and lock escalation. To see where each database stands: ```sql SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc FROM sys.databases; ``` ### Why NOLOCK is not a fix Adding `NOLOCK` hints, or using READ UNCOMMITTED, makes blocking appear to vanish by letting queries ignore other transactions’ locks. The cost is correctness: queries can return changes that are later rolled back, miss rows or read rows twice while data moves, and fail with error 601. `NOLOCK` still takes schema stability locks, so schema changes still block it, and it does nothing about writers blocking writers. If readers and writers blocking each other is the issue, RCSI solves it without returning wrong answers. ## Common patterns and typical fixes ### Key lookup deadlocks A query seeks on a nonclustered index, then looks up the remaining columns in the clustered index, holding a shared lock on the nonclustered key as it does. Meanwhile an update holds an exclusive lock on that clustered row and needs to change the nonclustered index. The graph typically shows key locks on two indexes of the same table. The usual fix is a covering index that adds the needed columns with `INCLUDE`, so the lookup disappears; RCSI also removes the reader’s shared locks. ### Out-of-order access One procedure updates orders and then customers; another updates customers and then orders. Under concurrent load they can deadlock. Access tables and rows in a consistent order throughout the codebase, and keep transactions short. ### Read-then-update conversions Two sessions read the same row under REPEATABLE READ or SERIALIZABLE, keep their shared locks, and then both try to update it. Neither can convert to an exclusive lock while the other’s shared lock remains. Reading with `UPDLOCK` makes the second session wait at the read instead of deadlocking at the write. ### Escalation during batch work Large deletes, archiving and imports are best broken into batches small enough to stay under the escalation threshold, each committed separately and supported by an index on the filter columns. Disabling escalation is possible but rarely the right first step, because every one of those locks then has to be held in memory. Whatever the pattern, applications should treat error 1205 as retryable: the victim’s transaction was rolled back cleanly, and resubmitting it usually succeeds. Retry logic does not replace fixing frequent deadlocks, but it stops occasional ones reaching users. If blocking or deadlocks are affecting a business-critical system, a focused performance investigation will usually identify the head blockers, the transactions and indexes involved, and the least disruptive fix. Talk to a SQL Server specialist at SQLCare and we can work through the evidence with you. --- # Why SQL Server transaction logs keep growing URL: https://www.sqlcare.co.uk/insights/why-sql-server-transaction-logs-keep-growing Topic: Operations Published: 2026-09-11 A growing transaction log is a symptom, not the problem. How to find what SQL Server is waiting for, fix the cause and free space without breaking your backup chain. A transaction log that keeps growing is one of the most common reasons a SQL Server ends up in trouble. The drive fills, the database stops accepting changes with error 9002, and someone reaches for a shrink command. The growth is almost always a symptom. SQL Server will tell you what the log is waiting for, and once you know that, the fix is usually straightforward and need not put your backups at risk. ## How the transaction log works Every change is written to the transaction log before it reaches the data files. That is what lets SQL Server roll back incomplete transactions, recover after a crash and restore to a point in time. The log is designed to be reused, not to grow indefinitely as a history file. Each physical log file is divided internally into virtual log files (VLFs). SQL Server fills one VLF after another and, at the end of the file, wraps round to the start – provided the earlier VLFs are no longer needed. Marking them reusable is called log truncation. Despite the name, truncation does not make the file smaller; it frees space inside it. A VLF can be reused only when nothing still needs any record in it. Anything that pins the oldest required record, such as an open transaction, a pending log backup or a replica that has not caught up, stops truncation. New records then need fresh space, so the file grows until autogrowth is disabled, a maximum size is reached or the disk fills, and error 9002 follows. ## Recovery models, and why FULL without log backups keeps growing - SIMPLE: the log is truncated automatically after a checkpoint. There are no log backups, so you can restore only to the end of a full or differential backup. - FULL: the log is truncated only by a log backup, and not by a copy-only one. In return, you can restore to any point covered by an unbroken chain of log backups. - BULK_LOGGED: like FULL, but some bulk operations, such as `BULK INSERT` and index rebuilds, can be minimally logged. Log backups are still required, and Microsoft documents point-in-time recovery as unsupported, so it is normally used only temporarily. New databases take their recovery model from `model`, which is FULL on Enterprise and Standard editions. If nobody schedules log backups, nothing truncates the log and it grows until it hits a limit, which makes this one of the most common causes of a runaway log. One twist catches people out: a new FULL database reuses its log as if it were SIMPLE until its first full backup. The log can stay small for months, then start growing the week someone adds a nightly full backup without log backups. If the database needs point-in-time recovery, schedule log backups at a frequency that matches how much work the business can afford to lose. If it does not, SIMPLE may be right, but make that a deliberate recovery decision, not a way to silence a disk alert. ## Finding out what the log is waiting for Start with `log_reuse_wait_desc` in `sys.databases`. It reflects the last checkpoint, so it can lag slightly. ```sql SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases ORDER BY name; ``` - NOTHING or CHECKPOINT: normal. - LOG_BACKUP: a log backup is needed before space can be reused. Log backups are missing, failing or too infrequent. - ACTIVE_TRANSACTION: a long-running transaction is holding the log. This applies in every recovery model, including SIMPLE. - REPLICATION: transactional replication or change data capture (CDC) has not processed the log. Check the Log Reader Agent or CDC capture job, and look for publications or CDC configuration left behind with no working agent. - AVAILABILITY_REPLICA: an availability group secondary has not caught up, in either synchronous or asynchronous commit mode. Look for a suspended, disconnected or slow secondary, and check `log_send_queue_size` and `redo_queue_size` in `sys.dm_hadr_database_replica_states`. - ACTIVE_BACKUP_OR_RESTORE: a data backup or restore is running. Log backups can still run during a slow full backup, but the log cannot be cleared past the point that backup needs until it finishes. To see how full the log is, run this in the database concerned: ```sql SELECT DB_NAME(database_id) AS database_name, total_log_size_in_bytes / 1048576.0 AS log_size_mb, used_log_space_in_bytes / 1048576.0 AS log_used_mb, used_log_space_in_percent, log_space_in_bytes_since_last_backup / 1048576.0 AS used_since_log_backup_mb FROM sys.dm_db_log_space_usage; ``` A large file with a low percentage used is not an emergency; the space will be reused. A high percentage used with a wait other than NOTHING or CHECKPOINT is what to act on. These DMVs need `VIEW SERVER STATE`, or the finer-grained performance state permissions introduced in SQL Server 2022. ## Long-running and open transactions For ACTIVE_TRANSACTION, find the transaction first. `DBCC OPENTRAN` reports the oldest active transaction in a database, with its session ID and start time: ```sql DBCC OPENTRAN ([YourDatabase]) WITH TABLERESULTS, NO_INFOMSGS; ``` For every database, with log usage and the source of each transaction: ```sql SELECT st.session_id, DB_NAME(dt.database_id) AS database_name, dt.database_transaction_begin_time, DATEDIFF(MINUTE, dt.database_transaction_begin_time, SYSDATETIME()) AS open_minutes, dt.database_transaction_log_bytes_used / 1048576.0 AS log_used_mb, dt.database_transaction_log_bytes_reserved / 1048576.0 AS log_reserved_mb, es.host_name, es.program_name, es.login_name FROM sys.dm_tran_database_transactions AS dt JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = dt.transaction_id JOIN sys.dm_exec_sessions AS es ON es.session_id = st.session_id WHERE dt.database_transaction_begin_time IS NOT NULL ORDER BY dt.database_transaction_begin_time; ``` Typical culprits are an application that never commits, a query window left open after `BEGIN TRAN`, a large delete or load run as one transaction, and index maintenance on large tables. The host, program and login columns usually tell you who to speak to. Be careful with `KILL`. The transaction rolls back, a large rollback can take a long time, and the log stays pinned until it completes. Where possible, have the owner finish or cancel the work, and break legitimate large operations into smaller batches. SQL Server 2019 and later offer accelerated database recovery (ADR), which truncates the log aggressively even during long transactions. It is off by default, stores row versions in the database, and its aggressive truncation is disabled when transactional replication or CDC is in use. It is worth evaluating, but it does not replace dealing with the transaction in front of you. ## Shrinking, growth settings and VLF counts Shrinking returns unused log space to the operating system. Once, after a one-off event, that can be reasonable. As a routine answer to growth it fails: - It does not touch the cause, so the file barely shrinks or grows straight back. - Growth is expensive. Before SQL Server 2022, log growth cannot use instant file initialisation, so new space is zeroed and the database can pause meanwhile. From SQL Server 2022, only increments of 64 MB or less benefit. - Repeated small shrink and grow cycles leave the log split into very many VLFs. Microsoft’s guidance is that shrinking should not be regular maintenance and `AUTO_SHRINK` should stay off. Size the log for its real peak, usually set by the largest index maintenance job, the largest batch or the log generated during a full backup, and leave it there. Set autogrowth as a fixed size rather than a percentage. Microsoft recommends no more than 1,024 MB for log files, and the default since SQL Server 2016 is 64 MB. Each growth adds VLFs. From SQL Server 2014, a growth smaller than one eighth of the current log size adds one VLF. Otherwise: | Size of growth | VLFs created | | --- | --- | | Less than 64 MB (64 MB or less in SQL Server 2022 and later) | 4 (1 in SQL Server 2022 and later) | | 64 MB up to 1 GB | 8 | | More than 1 GB | 16 | A log grown to 50 GB in 10 MB steps ends up with thousands of small VLFs. Too many VLFs slows startup, restores and log backups, and delays replication, CDC and availability group redo. Microsoft suggests keeping the total to several thousand at most, with serious symptoms at hundreds of thousands. ```sql SELECT d.name AS database_name, COUNT(*) AS vlf_count, SUM(CASE WHEN li.vlf_active = 1 THEN 1 ELSE 0 END) AS active_vlfs FROM sys.databases AS d CROSS APPLY sys.dm_db_log_info(d.database_id) AS li WHERE d.state_desc = N'ONLINE' GROUP BY d.name ORDER BY vlf_count DESC; ``` `sys.dm_db_log_info` requires SQL Server 2016 SP2 or later. Where a count is excessive, the remedy is one planned shrink, growing the log back to its working size in one or a few large steps, and correcting autogrowth – in a quiet period, with a recent restorable backup, after the original cause is fixed. ## What to do when the disk is nearly full The aim is to buy space safely and remove the cause without losing the ability to recover. - Read the wait for the affected database, and check file sizes, growth settings (0 means autogrowth is off) and free space with the query below. - For LOG_BACKUP, take a log backup using the existing job, or to a location with space, ideally not the filling volume. Keep the file with the rest of the chain, because any restore past this point needs it. Occasionally a second log backup is needed before space is released. Then find out why scheduled backups stopped. - For ACTIVE_TRANSACTION, REPLICATION or AVAILABILITY_REPLICA, resolve the transaction, restart the stalled agent, or resume or repair the replica. - Buy headroom if needed by moving unrelated files, extending the volume or, temporarily, adding a second log file on another volume and removing it once it is no longer in use. - Tidy up afterwards: consider a one-off shrink, check the VLF count and set autogrowth properly. ```sql SELECT DB_NAME(mf.database_id) AS database_name, mf.name AS logical_file_name, mf.size / 128 AS size_mb, CASE WHEN mf.is_percent_growth = 1 THEN mf.growth END AS growth_percent, CASE WHEN mf.is_percent_growth = 0 THEN mf.growth / 128 END AS growth_mb, vs.volume_mount_point, vs.available_bytes / 1048576 AS volume_free_mb FROM sys.master_files AS mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs WHERE mf.type_desc = N'LOG' ORDER BY volume_free_mb, size_mb DESC; ``` Common emergency moves that make things worse: - Switching to SIMPLE and back. It breaks the log backup chain: point-in-time recovery is lost, and log backups fail, until a new full or differential backup is taken. If it has already happened, take that backup now. - Backing up the log to `NUL`. Space is freed, but the log records are discarded, leaving a gap no restore can cross. - Deleting the log file, or detaching the database to get rid of it. The log is needed to keep the database consistent, and removing it can leave the database unrecoverable. - Scheduling a shrink job. It hides the symptom until the disk fills again. If a log keeps growing and the cause is not clear, or the disk is close to full on a system the business depends on, talk to a SQL Server specialist before running anything that changes the database. The queries above are where we start, and the aim is always the same: free the space, fix the cause and keep the backup chain intact. --- # Why is SQL Server suddenly slow? URL: https://www.sqlcare.co.uk/insights/why-is-sql-server-suddenly-slow Topic: Performance Published: 2026-09-11 When a database that was fine yesterday is slow today, the first half hour matters. This is the order we check things in, and the common fixes that destroy the evidence. When a SQL Server system that was fine yesterday is slow today, there is pressure to do something quickly. The most useful thing you can do in the first half hour is collect evidence before anything changes, because several common “fixes” destroy the information needed to find the cause. This is the sequence we follow when a business-critical database suddenly slows down. ## Confirm it really is the database Start by narrowing the problem. Is everything slow, or one screen, report or batch job? Is it every user, or one site or application server? When did it start? A single slow process points towards a query or plan problem; everything slowing at once points towards blocking, resource pressure or infrastructure. Then check whether SQL Server is actually busy. If the application servers are running flat out or waiting on another service, the database may be innocent. Inside SQL Server, `ASYNC_NETWORK_IO` is a useful referee: it means SQL Server has results ready and is waiting for the client to accept them. Sessions in that wait usually indicate an application processing large result sets row by row, or a slow network, rather than a slow database. A query that runs quickly in Management Studio but slowly from the application does not prove the application is at fault either. The two connections often use different `SET` options (`ARITHABORT` is the usual one), so they get separate cached plans that may have been compiled for different parameter values. ## What changed? Performance rarely changes for no reason. Build a timeline of what happened around the time the slowdown began: - Deployments. New application releases, changed stored procedures, ORM upgrades and new reports all change the queries the server receives. - Data growth. A table that grows past a certain size can tip the optimiser into a different plan, or turn a tolerable scan into an intolerable one. - Statistics. From compatibility level 130, automatic statistics updates trigger after the smaller of 500 plus 20% of rows, or the square root of 1,000 times the row count, in modifications. That suits large tables better than the old rule, but tables with ascending keys such as dates still get queries for recent rows the histogram knows nothing about. - Plan regressions and parameter sensitivity. A plan compiled for an unusual parameter value is reused for every other value, and a recompile, statistics update or plan eviction can swap a good plan for a bad one with no code change. SQL Server 2022 added Parameter Sensitive Plan optimization at compatibility level 160, which can keep several plans per query, but only for equality predicates. - Maintenance jobs. Index maintenance, integrity checks and backups that have drifted into business hours, or a job that updates statistics with default sampling straight after an index rebuild has produced full-scan statistics. - Index changes. A dropped or disabled index, or a new one that changed plan choices for other queries. - Patching and upgrades. Cumulative updates, Windows updates, driver or firmware changes, and especially a raised database compatibility level after an upgrade, which changes optimiser behaviour including cardinality estimation. - VM and storage changes. A host migration, a move to a different storage tier, a reduced vCPU or memory allocation, a busier host, snapshot or backup activity at the hypervisor, or a power plan that throttles the CPU. ## Where to look first The dynamic management views below are read-only. On SQL Server 2022 and later they need `VIEW SERVER PERFORMANCE STATE`; on earlier versions, `VIEW SERVER STATE`. Most of their counters reset when the service restarts, which is one of several reasons not to restart yet. ### Current activity and blocking Look at what is running now. `sys.dm_exec_requests` shows each active request with its wait type, how long it has been waiting, CPU and elapsed time, and a `blocking_session_id` if another session is holding it up. ```sql SELECT r.session_id, r.status, r.blocking_session_id, r.wait_type, r.wait_time AS wait_ms, r.cpu_time AS cpu_ms, r.total_elapsed_time AS elapsed_ms, r.logical_reads, DB_NAME(r.database_id) AS database_name, t.text AS batch_text FROM sys.dm_exec_requests AS r JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t WHERE s.is_user_process = 1 AND r.session_id <> @@SPID ORDER BY r.total_elapsed_time DESC; ``` If many requests show `LCK_M_*` waits and a non-zero `blocking_session_id`, you have a blocking problem, and the priority is finding the session at the head of the chain rather than tuning individual queries. `sys.dm_os_waiting_tasks` gives the same picture per task, with the locked resource in `resource_description`. ### Wait statistics `sys.dm_os_wait_stats` records cumulative time spent in each wait type since the service started. The totals are dominated by harmless background waits and by history, so take two snapshots a few minutes apart while the system is slow and compare them. As a rough guide, `PAGEIOLATCH_*` and `WRITELOG` point towards storage or large reads, `LCK_M_*` towards blocking, `RESOURCE_SEMAPHORE` towards queries queuing for memory, and `SOS_SCHEDULER_YIELD` towards CPU pressure. Waits describe symptoms rather than causes: heavy `PAGEIOLATCH_SH` is as often a query reading far too much data as it is slow disks. ### Expensive queries and Query Store `sys.dm_exec_query_stats` aggregates CPU, reads and duration for plans currently in cache. Its rows disappear when a plan is removed from cache, so check `creation_time` before drawing conclusions. ```sql SELECT TOP (10) qs.total_worker_time / 1000 AS total_cpu_ms, qs.execution_count, qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS avg_logical_reads, qs.creation_time, qs.last_execution_time, SUBSTRING(t.text, qs.statement_start_offset / 2 + 1, (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t ORDER BY qs.total_worker_time DESC; ``` Query Store suits the “what changed” question better, because it keeps plan and runtime history inside the database, where it survives restarts. Its Regressed Queries report shows queries whose performance has changed and the plans involved, and a known-good plan can be forced while the cause is fixed. Available since SQL Server 2016, it is only enabled by default for databases created on SQL Server 2022 or later, so check rather than assume. ### Storage latency `sys.dm_io_virtual_file_stats` records reads, writes and the time spent waiting for them on every data and log file. ```sql SELECT DB_NAME(vfs.database_id) AS database_name, mf.physical_name, vfs.num_of_reads, vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS avg_read_ms, vfs.num_of_writes, vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_ms FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs JOIN sys.master_files AS mf ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id ORDER BY avg_read_ms DESC; ``` These averages cover the whole period since startup and can hide a recent problem, so sample twice and compare. Microsoft’s troubleshooting guidance treats I/O waits consistently above roughly 10 to 15 milliseconds as a bottleneck. If storage is slow, establish whether SQL Server is generating the load (a plan change that scans a large table can saturate good storage) or the storage itself has changed. ### Memory grants and memory pressure Sorts and hash operations need a memory grant before they run. When grants are too large, other queries queue for memory and show `RESOURCE_SEMAPHORE` waits. `sys.dm_exec_query_memory_grants` lists current grants: rows where `grant_time` is NULL are still waiting, and comparing `requested_memory_kb` with `used_memory_kb` shows whether estimates are far out. Oversized grants usually come from bad row estimates, which leads back to statistics and plans. ## Why adding CPU or memory often doesn’t fix it Adding hardware is tempting because it can be done without understanding the problem. It often disappoints: - Blocked sessions are waiting for locks, not for CPU. More cores do not make another transaction commit sooner. - A plan that scans millions of rows instead of seeking to a handful is orders of magnitude slower. Faster hardware narrows that gap only slightly. - Memory grant problems come from estimates. A query that asks for far too much memory will ask for far too much on a bigger server. - New memory is not used until `max server memory` allows it, and on a VM the host has to be able to provide it. - SQL Server is usually licensed per core, so extra vCPUs can bring a significant licence cost for no gain. Genuine capacity problems do exist, and more memory can be the right answer to sustained heavy reads. That should be a conclusion drawn from evidence, not the first move. ## Quick fixes that make things worse ### Restarting to “clear the cache” A restart often appears to work, because the bad plan is discarded and the next compilation happens to choose a better one. It also wipes the wait, query and file statistics you needed, empties the buffer pool so the server runs on a cold cache for a while, and ends in-flight work. Long transactions have to be rolled back during recovery, which can keep the database unavailable for some time unless accelerated database recovery is enabled (SQL Server 2019 and later). And the bad plan tends to return the next time the wrong parameter value is compiled first. ### Shrinking databases Shrinking moves pages from the end of a file into free space nearer the start, which fragments indexes. If the free space was needed, the file grows again, and growth events cost performance while they happen. Shrinking is for recovering space after a large one-off deletion, not a tuning step. ### Blanket index rebuilds Rebuilding every index generates a large volume of transaction log, consumes CPU and I/O, and when run offline holds locks that block users. When a rebuild does seem to help, the reason is usually that it updated statistics with a full scan and caused plans to recompile. Microsoft’s index maintenance guidance makes the same point: updating the relevant statistics often achieves that benefit at a much lower cost. ## When to call a specialist Many slowdowns can be resolved in-house with the steps above. It is worth bringing in a SQL Server specialist when: - the system is business-critical and the slowdown is costing money or customers while you investigate; - the evidence points in several directions at once, or the obvious fix has not held; - the likely remedy involves forcing plans or changing indexes, isolation levels or compatibility levels on production; - you suspect the storage or virtualisation layer and need hard evidence to take to the infrastructure team or hosting provider; - the problem keeps coming back, which usually means the root cause has not been found. Whoever investigates, the most valuable handover is untouched evidence: when the problem started, what changed, and the output of the queries above captured while the system was slow. If your SQL Server has slowed down and you would like a clear explanation rather than another restart, a focused performance investigation is a sensible next step. Talk to a SQL Server specialist at SQLCare about what you are seeing, and we will tell you plainly what we would look at first. --- ## Contact Telephone +44 (0)303 666 5485 · Email info@eplatforms.com · https://www.sqlcare.co.uk/contact