All system databases, except the resource database, can be moved to new locations to help balance I/O load. To move the msdb, model, and tempdb databases, perform the following steps: For each file to be moved, execute the ALTER DATABASE … MODIFY FILE statement. Stop the instance of SQL Server. Move the files to the … Continue reading Moving the msdb, model, and tempdb databases files
Understanding parallelism on SQL Server
Parallelism refers to multiple processors cooperating to execute a single query at the same time. Parallel execution involves the overhead of synchronizing and monitoring the tasks. So, that's why parallel plans are considered expensive operations. The query optimizer determines whether a parallel plan should be used based on the configuration and the query cost. A configuration … Continue reading Understanding parallelism on SQL Server
There is insufficient system memory in resource pool
Doing crash and recovery tests on my local machine I got the SQL Server instance not going online. After trying the third time to bring my instance online thinking was something else problem I saw the SQL Server errolog file and I could see the problem. Not enough memory, but wasn't on my machine, was … Continue reading There is insufficient system memory in resource pool
SQL Examiner Tool
I like tools and I’ve used a couple of them. I had the opportunity to try SQL Examiner to compare database structures and even though compare data. The tool is quite simple to configure the options. Talking about comparison, well we compare one database with another. Imagine a situation, there are two environments (development and … Continue reading SQL Examiner Tool
10 Configurations Best Practices
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 … Continue reading 10 Configurations Best Practices
