The use of SANs in today network environment is almost common place, but is the performance requirements of the systems using those SANs considered in the design on the SAN setup.
A typical problem I find is people are not considering how the SAN should be setup to get the best performance, primarily for DAX we are talking about the considerations for SQL server, regardless if SQL is visualized or physical.
Using the Database Engine Advisor to analyse a SQL query can give you an insight into where to look for problems with performance. Recently I have a problem opening the advisor from a query window recieving an error: “Failed to connect to an IPC Port: The system cannot find the file specified”.
The problem was caused by a previous use of the system that had not been shutdown cleanly. The simplest way to resolve this problem is to open the windows task manager and end the process for DTAShell.exe.
During data migration it is often necessary to pull data from another SQL server database. It can also be necessary for integration purposes. Below is some sample code that can be used to get data from another system.
I had a need to track down which columns in the system had a particular string value. I used the below stored procedure to identify the tables and columns that contained a particular string value.
This script is designed to only find string values within the system, only looking at string fields that are large enough store the entire string.
Getting the most performance out of SQL server is always important when using DAX. As a result I constantly read the SQL server articles looking to ensure everything has been considered.
SQL server reads and writes its data and log files in 64K blocks, so using a 64K block size for the hard drive format seems to make sense. But what effect will this have?
It is important to monitor your database growth, both for system maintenance, but also to plan for long term system resource requirements and potential performance issues.
There is no out-of-the-box way to show the growth information in SQL so you need to store some information on a periodic basis to be able to perform the analysis. This can be achieved with the following scripts: