The first checklist before start using any application using SQL Server should be the default configurations.
What configurations I used to change?
- Max Server Memory: SQL Server will grab all memory available and that will cause concurrency with the OS
- MAXDOP: for OLTP also yes, the good thing is to use it
- Cost Threshold for Parallelism: the default value is too low, and the number will depend on the environment, I used to monitor and change as needed. I use to start with 40
- Model File Sizes: the model database will provide information when need to create more databases. (initial size and autogrowth are way too small and need to avoid file system fragmentation)
- Tempdb Files: look how many CPUs in the server and if the number is less than 8, so would be the number of CPUs, if it is more, so, start with 8 and the files should be the same size and the same autogrowth.
- Database Mail: used to send alerts, very important to get notified when something bad happens.
- Backup Compression: the benefit will be much more than the CPU cost.
- Remote Dedicated Administrator Connection: provides dedicated CPU, memory, and scheduler and by default only works via RPD or physically on server
- Maintenance: start as soon as possible with checkdb, index maintenance, statistics, backups. In this case, I like Ola Hallengren SQL Server solution.
- The last but not least, Security: this is the most important in the list, user accounts or even DBAs as sysadmin, look my tip in MSSQLTips. In addition, disable sa, disable unnecessary logins.
SQL Server has many undocumented functions and commands. I will write series of posts with functions to have a library online.
The first, I’d like to write about %%PhysLoc%% and the function fn_PhyslocCracker
With this function, I can see my table physical location and use DBCC PAGE to see the data in each page.
FROM dbo.MyTable AS m
CROSS APPLY fn_PhyslocCracker(%%physloc%%) plc;
DBCC PAGE(MyDatabse, File_ID, Page_ID, Type) WITH TABLERESULTS
The most common and widely used index that we know are nonclustered index. These indexes are created using the famous b-tree (balanced tree). B-tree is a data-structured tree where we have the root and leaves.
A nonclustered index contains the index key values and row locators that point to the storage location of the table data. Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.
These indexes need a regular maintenance because as data is updated, inserted or deleted these indexes are updated for the new data or delete the reference of a particular deleted data.
When this happen we called fragmentation and Brent Ozar can explain more about it.
The objective here is about maintenance the index, well not exactly who to do this, but what do not do. I’ve seen in same cases a good reindex job, everything ok, tables distributed by number of rows, cluster, then no cluster, but after all of it a step with shrink.
Do not use shrink database, this is not a good idea after a reindex. What is this? What will do in the database? In MSDN page we can see this:
Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can be deallocated and returned to the file system
The process SQL uses is ugly and results in index fragmentation again and all that work to reindex was thrown away. If don’t belive me, well Paul Randal explain something in his article.