More and more people are taking the ‘There is no database’ statement to it’s limit, i think it’s time to share some insights into how we could let SQLServer help us when investigating performance issues.
When it comes to performance on SQLServer these are the two most important factors that have a direct impact on system performance:
As we are running on a filesystem where disk fragmentation is costly and unavoidable, there are some settings to think about when creating a new database:
- Initial Size (MB): set this both for data and log to a reasonable size. Don’t start with the defaults of 3 Mb data and 1 Mb log, but instead with about 100 Mb for data and 50 Mb on logs (depending on the recovery model), as increasing the size will impact performance and has a major impact to fragmentation on your hard drive.
- AutoGrowth: Default is to grow 1 Mb a time, set this to a percentage or at least 1/10 of the initial size.
Remember SQLServer is your friend, using Actual Query plan while executing a query can give you hints about the underlying problem. But in general if you don’t know where to start you could use built-in statistics:
SELECT TOP 25
This query will output the top 25 missing indexes ordered by estimated impact, look at indexes where the avg estimated impact is above 100000 and are frequently used (Try to avoid the INCLUDE indexes at first).
If the above query does not help you in any way, it could mean that the indexes exist but have been fragmented too much.
SELECT dbschemas.[name] as 'Schema',
Look for indexes that have a fragmentation level higher than 30 % and a high page count (by default on sql server the page size is 8K).
ALTER INDEX PK_TABLE ON [dbo].[TABLE]
Usually, the higher the better (max is 100), but it depends on how often the table changes and what the index contains. Two examples:
- PK on a int identity key, use fill factor 100% as new records are always created at the bottom (normally index fragmentation on these should be low, or a lot of records have been deleted)
- PK on a guid key, use fill factor depending on how often new records are added (start by 80% or 90%) and monitor page splits to fine tune (see query below)
select Operation, AllocUnitName, COUNT(*) as NumberofIncidents