Saturday 12 April 2014

SQL Server performance tips


1. Question=>
 Is the following SQL good or bad practice from a performance perspective?
Searching for all rows of the year 2012:
CREATE INDEX tbl_idx ON tbl (date_column); 
SELECT text, date_column FROM tbl WHERE datepart(yyyy, date_column) = 2012;Given 
Answer- Good practice There is no major improvement possible.
Explanation-Wrapping the table column in a function renders the index useless for this query.

Write queries for continuous periods as explicit range condition: 
SELECT text, date_column FROM tbl WHERE date_column >= CAST('2012-01-01' AS DATE) AND date_column < CAST('2013-01-01' AS DATE)
See also: Using DATE columnsAlternatively, but less desirable: Indexing Computed Columns 

2. Question Is the following SQL good or bad practice from a performance perspective?
To find the most recent rowCREATE INDEX tbl_idx ON tbl (a, date_column); SELECT TOP 1 id, date_column FROM tbl WHERE a = @a ORDER BY date_column DESC 
Answer Bad practiceThere is a major improvement possible.
Explanation The statement can be executed as an indexed Top-N query. It performs just like a B-Tree traversal only so it's very efficient. The trick is that the index supports the where as well as the order by clause. The database uses the index to find the last entry that matches the where clause and takes it as result. There is no need to actually perform a sort for the order by. See also: Querying Top-N Rows in my Book SQL Performance Explained 

3. Question Is the following SQL good or bad practice from a performance perspective? 
Two queries, searching by a common column:
CREATE INDEX tbl_idx ON tbl (a, b); SELECT id, a, b FROM tbl WHERE a = @a AND b = @b; 
SELECT id, a, b FROM tbl WHERE b = @b; Given 
Answer Bad practice There is a major improvement possible. 
Explanation The index covers the first query only, the second query cannot use the index to the best extent possible. Changing the column order makes the index suitable for both queries—without additional overhead. The index should therefore look like this (columns exchanged): CREATE INDEX tbl_idx ON tbl (b, a); See also: Multi-Column Indexes 

4. Question Is the following SQL troublesome or bulletproof from a performance perspective? 
Searching within a string:
CREATE INDEX tbl_idx ON tbl (text);
SELECT id, text FROM tbl WHERE text LIKE '%TERM%';Given
Answer Troublesome There is high risk for performance problems.
Explanation LIKE expressions starting with a wildcard cannot use an index to locate the matching entries. There is no simple way to tune such a query. Use another access path if possible (e.g., additional where conditions). Otherwise consider using a full-text index. See also: A visual explanation whySQL's LIKE is slow

5. Question How will the change affect query performance?
Current situation, selecting about hundred rows out of a million
CREATE INDEX tbl_idx ON tbl (a, date_column);
SELECT date_column, count(*) FROM tbl WHERE a = @a GROUP BY date_column;
Changed query, selecting about ten rows out of a million
SELECT date_column, count(*) FROM tbl WHERE a = @a AND b = @b GROUP BY date_column;Given
Answer The query will be much slower (impact >10%)
Explanation The query will be much slower—regardless of the data. The original query is executed as an index-only scan. It doesn't need to access the table because the index covers the entire query—all referenced columns are covered in the index. Although the additional where clause reduces the number of returned rows, it requires a table access to fetch the column B, which is not included in the index. That means that the new query cannot run as an index-only scan; it must access the table as well. This access is additional work that slows the query down—regardless whether the final result is smaller due to this filter.See also: Index-Only Scan in my book SQL Performance Explained.

Create symlink inside Virtual Machine (Virtual Box) Host's shared folder

Many of you folks had faced this issue while installing Node.js and its other module inside the shared folder of Host especially when host is Windows based machine and guest is Linux.

Here is the trick :-
  1. Shutdown or halt your VM.
  2. Go inside your Virtual Box installed folder (default location: C:\Program Files\Oracle\VirtualBox)
  3. Execute this command to list your VMs
    1. vboxmanage list vms
    2. Output will be something like this :
      • "Lubuntu" {199bfa3d-ee72-4685-bf7d-9ec52e3a7de4}
  4. Take a note of that VM name (in this case its Lubuntu)
  5. Now Execute this command :-
    • VBoxManage setextradata Lubuntu VBoxInternal2/SharedFoldersEnableSymlinksCreate/vagrant 1
    • *Note that Instead of Lubuntu enter your VM name and vagrant will be replaced by your shared folder.
    • You can also verify the added configuration by executing this command:
    • VBoxManage getextradata Lubuntu enumerate
  6. Now start your VM as Administrator, I usually do that via command line so even if you want to start your VM as Admin via command line just open Command Prompt as Administrator.
  7. Now if you want to create any symbolic link inside shared folder it will be able to do that. Like you are inside your shared folder (on Linux) and try to enter this command it will successfully create it:
    • ln -s /path/to/actual/folder link_name
  8. If any problem persists then you can ask me out in comments!