Thursday, February 25, 2016

Simple Table Partitioning

I've recently presented my SSMS Query Shortcuts and had a question from the audience if my queries cover Database Partitions and Partitioned tables.

I know everybody want that and I wanted to write these queries long time ago. So, it is the time now.

At first, we will establish Partitioning Case.

File Partitioning

First, will create test database with additional partitions and files.

USE master
 ( NAME = N'TestPartitions1', 
 FILENAME = N'D:\SS2014\Data\TestPartitionsPrimary1.mdf', SIZE = 20MB),
 ( NAME = N'TestPartitions2', 
 FILENAME = N'D:\SS2014\Data\TestPartitionsPrimary2.ndf', SIZE = 1MB),
 ( NAME = N'Partition1', 
 FILENAME = N'D:\SS2014\Data\TestPartitions1.ndf', SIZE = 1MB),
 ( NAME = N'Partition2', 
 FILENAME = N'D:\SS2014\Data\TestPartitions2.ndf', SIZE = 1MB),
 ( NAME = N'Partition3', 
 FILENAME = N'D:\SS2014\Data\TestPartitions3.ndf', SIZE = 1MB),
 ( NAME = N'Partition3A', 
 FILENAME = N'D:\SS2014\Data\TestPartitions3A.ndf', SIZE = 1MB)
 ( NAME = N'TestPartitions_log', 
 FILENAME = N'D:\SS2014\Data\TestPartitions_log.ldf', SIZE = 1MB)
By that code  we create new Database "TestPartitions" with two files in PRIMARY file group. Besides of "Primary" we created two groups with one file each and third group with two files.
Here is how result of that creation looks in SSMS:
But we do not like to go for that information manually to Management Studio for each database on every server. Right?

To automate the process will write a query:

USE [TestPartitions]
SELECT ISNULL(,'LOG') AS File_Group_Name
, CASE fg.is_read_only WHEN 0 THEN 'No' 
 WHEN 1 THEN 'Yes' ELSE 'N/A' END AS [Group Read Only]
, CASE fg.is_default WHEN 0 THEN 'No' 
 WHEN 1 THEN 'Yes' ELSE 'N/A' END AS [Default Group]
, mf.file_id
, AS [File_Name]
, mf.type_desc AS [File_Type]
, mf.physical_name
, mf.state_desc AS [File_State]
, CAST(mf.size/131072. AS DECIMAL(7,3)) AS [File_Size_Gb]
, CASE mf.max_size WHEN 0 THEN 'No Growth' WHEN -1 THEN 'Full Disk' 
 ELSE CAST(CAST(mf.max_size/131072. AS DECIMAL(7,3)) AS VARCHAR) END 
 AS [Max_Size_Gb]
, CASE mf.is_percent_growth WHEN 0 
 THEN CAST(CAST(mf.growth/128. AS DECIMAL(10,3)) as VARCHAR) + ' Mb'
 ELSE CAST(growth as VARCHAR) + ' %' END as [AutoGrowth]
, CASE mf.is_read_only WHEN 0 THEN 'No' ELSE 'Yes' END AS [File Read Only]
, CAST(ROUND(( fs.io_stall_read_ms / ( 1.0 + fs.num_of_reads ) ),3) as FLOAT) 
 as [Avg Read Wait, ms]
, CAST(ROUND(( fs.io_stall_write_ms / ( 1.0 + fs.num_of_writes ) ),3) as FLOAT) 
 as [Avg Write Wait, ms]
FROM sys.master_files AS mf
INNER JOIN sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS fs 
 ON fs.file_id = mf.file_id AND fs.database_id = mf.database_id
LEFT JOIN sys.filegroups AS fg 
 ON mf.data_space_id = fg.data_space_id
ORDER BY mf.data_space_id, mf.file_id
Here we can see not only how File Groups and Files were setup, but also some operational information
like: File_id, File Physical Name, File State, Average Read/Write File statistics.

Table Partitioning

For Table Partitioning we have to create Partitioning Functions and Partitioning Schemas.
In this example I will create Functions' Partitioning by DateTime field.
use [TestPartitions]
prt_Test_Left (DATETIME) AS RANGE LEFT FOR VALUES('2014-01-01','2016-01-01');
AS PARTITION prt_Test_Left TO (FileGroup1,FileGroup2,FileGroup3);
prt_Test_Right (DATETIME) AS RANGE RIGHT FOR VALUES('2014-01-01','2016-01-01');
AS PARTITION prt_Test_Right TO (FileGroup3,FileGroup2,FileGroup1);
I've created two functions with "Left" and "Right" side boundaries and with three ranges:
Range 1: Before 1/1/2014
Range 2: Between 1/1/2014 and 1/1/2016
Range 3: After 1/1/2016

As you can see, in the second partitioning schema, just for sake of experiment, I've purposely mixed File Groups in reverse order.

Here is what we can see in SSMS for Functions and Schemas we've just created:
That is really not so much.That means we need a query for more details and some automation:
SELECT as Partition_Schema, as Partition_Function
, pf.modify_date as Last_Modified
, CASE pf.boundary_value_on_right 
 WHEN 0 THEN 'LEFT' ELSE 'RIGHT' END as Function_Type
, R1.value as Min_Border_Value, R2.value as Max_Border_Value
, ds.destination_id as Partition_Order
, as [FileGroup_Name]
, SUM(IsNull(AU.total_pages,0)) as total_pages
, SUM(IsNull(AU.used_pages,0)) as used_pages
, SUM(IsNull(AU.data_pages,0)) as data_pages
, as [File_Name], sf.filename as Physical_File_Name
FROM sys.partition_schemes AS ps WITH (NOLOCK)
INNER JOIN sys.destination_data_spaces AS ds WITH (NOLOCK)
 ON ps.data_space_id = ds.partition_scheme_id
INNER JOIN sys.partition_functions AS pf WITH (NOLOCK)
 ON pf.function_id = ps.function_id 
 ON FG.data_space_id = ds.data_space_id
INNER JOIN sys.sysfiles AS sf  WITH (NOLOCK)
 ON sf.groupid = ds.data_space_id
LEFT JOIN sys.partition_range_values AS R1 WITH (NOLOCK)
 ON R1.function_id = pf.function_id 
  and R1.boundary_id + 1 = ds.destination_id
LEFT JOIN sys.partition_range_values AS R2 WITH (NOLOCK)
 ON R2.function_id = pf.function_id 
  and R2.boundary_id = ds.destination_id
LEFT JOIN sys.allocation_units AS AU WITH (NOLOCK)
 ON AU.data_space_id = ds.data_space_id
GROUP BY,, pf.modify_date, pf.boundary_value_on_right
 , R1.value, R2.value, ds.destination_id,,, sf.filename
ORDER BY, ds.destination_id
As the result we can see following:

We can see here:
1. Function to Schema relationships;
2. Function Range Type;
3. Bordering values;
4. Associated File Groups and Partitions;
5. Space usage statistics per File/Partition.

Create Partitioned Tables

We will create two tables. One in each schema and populate them with the similar data:
CREATE TABLE tbl_Test_Left(F1 DATETIME, F2 VARCHAR(MAX)) ON sc_Test_Left(F1);
CREATE TABLE tbl_Test_Right(F1 DATETIME, F2 VARCHAR(MAX)) ON sc_Test_Right(F1);
INSERT INTO tbl_Test_Left(F1) VALUES ('2013-06-01')
GO 15
INSERT INTO tbl_Test_Left(F1) VALUES ('2014-06-01')
GO 20
INSERT INTO tbl_Test_Left(F1) VALUES ('2015-06-01')
GO 10
INSERT INTO tbl_Test_Left(F1) VALUES ('2016-06-01')
GO 5
INSERT INTO tbl_Test_Right(F1) VALUES ('2013-06-01')
GO 15
INSERT INTO tbl_Test_Right(F1) VALUES ('2014-06-01')
GO 20
INSERT INTO tbl_Test_Right(F1) VALUES ('2015-06-01')
GO 10
INSERT INTO tbl_Test_Right(F1) VALUES ('2016-06-01')
GO 5
Following insertion was performed:
1. 15 records with before 1/1/2014 - are supposed to go to Range 1;
2. 20+10 records between 1/1/2014 and 1/1/2016 - are supposed to go to Range 2;
3. 5 records with after 1/1/2016 - are supposed to go to Range 3;

 In order to see records' allocations we can run following query:
SELECT as [Schema]
 , as Table_Name
 , IsNull(,'HEAP') as Index_Name
 , as Partition_Schema
 , as Partition_Function
 , pf.modify_date as Last_Modified
 , PA.partition_number as [Partition]
 , CASE pf.boundary_value_on_right 
  WHEN 0 THEN 'LEFT' ELSE 'RIGHT' END as Function_Type
 , R1.value as Min_Border_Value
 , R2.value as Max_Border_Value
 , as [FileGroup_Name]
 , PA.rows
 , SUM(AU.total_pages) as total_pages
 , SUM(AU.used_pages) as used_pages
 , SUM(AU.data_pages) as data_pages
 , as [File_Name]
 , sf.filename as Physical_File_Name
FROM sys.indexes AS i WITH (NOLOCK)
INNER JOIN sys.partition_schemes AS ps WITH (NOLOCK)
 ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions AS pf WITH (NOLOCK)
 ON pf.function_id = ps.function_id 
INNER JOIN sys.partitions AS PA WITH (NOLOCK) 
 ON PA.object_id = i.object_id AND PA.index_id = i.index_id 
INNER JOIN sys.allocation_units AS AU WITH (NOLOCK) 
 ON (AU.type IN (1, 3) AND AU.container_id = PA.hobt_id) 
  OR (AU.type = 2 AND AU.container_id = PA.partition_id) 
 ON i.object_id = o.object_id 
 ON o.schema_id = s.schema_id 
 ON FG.data_space_id = AU.data_space_id
INNER JOIN sys.sysfiles AS sf WITH (NOLOCK)
 ON sf.groupid = AU.data_space_id
LEFT JOIN sys.partition_range_values as R1 WITH (NOLOCK)
 ON R1.function_id = pf.function_id 
  AND R1.boundary_id + 1 = PA.partition_number
LEFT JOIN sys.partition_range_values as R2 WITH (NOLOCK)
 ON R2.function_id = pf.function_id 
  AND R2.boundary_id = PA.partition_number
GROUP BY,,, PA.partition_number, R1.value, R2.value
 ,,, pf.boundary_value_on_right, pf.modify_date
 ,, PA.rows,, sf.filename
ORDER BY, PA.partition_number 
Unfortunately, because we use more than one file per File Group, our Row and Page Counts are aggregated by a Group and it might bring some inconvenience because of the duplication.
 That duplication can be avoided by using "sys.dm_db_database_page_allocations", but for huge tables, which we usually partition,  that DMV will work too slow and I wouldn't recommend using it.

Left and Right Ranges

I did not stop on that before because I want demonstrate it with an example. Jut do couple of more inserts and then run the previous query.
INSERT INTO tbl_Test_Left(F1) VALUES ('2014-01-01'), ('2016-01-01')
INSERT INTO tbl_Test_Right(F1) VALUES ('2014-01-01'), ('2016-01-01')
As you can see, dates 1/1/2014 and 1/1/2016 were placed on the left side of tbl_Test_Left Table and on the right side of tbl_Test_Right Table.
That is their difference.

Splitting the Range

Usually new Partition Ranges are added to the end of a Function/Schema, but in my example I'll demonstrate how to split middle Partition.
At first we create new File Group. Then make it next within a schema and then split a Function:
ALTER DATABASE TestPartitions 
 ADD FILE ( NAME = N'Partition4', 
 FILENAME = N'D:\SS2014\Data\TestPartitions4.ndf', SIZE = 1024KB) 
ALTER PARTITION FUNCTION prt_Test_Left () SPLIT RANGE ('2015-01-01');
All records, which are Less than 1/1/2015 and more than 1/1/2014, were moved to the new partition.

I hope you liked this post and if you know any other interesting way to play with partitions please let me know.

Saturday, February 13, 2016

SSMS Query Shortcuts

I've recently made a presentation for that topic and decided to post some of it's materials here.

*That post is going to improve your working experience with SSMS.

Native SSMS Functional Shortcut Keys

If you work with SQL Server for a long time you've probably learn some Keyboard combinations to speed up your administration or development process.
The full list of SSMS Shortcut keys you can find in MSDN

I will try to re-categorize the most interesting ones:

The most popular are:
F5 - Execute selected query or All queries within the current Tab;
Ctrl-R - Show/Hide Result Panel
Ctrl-Tab - Switch between tabs;

Windows wide ones:
Ctrl-N - Create new Tab;
Ctrl-S - Save Current tab to a file;
Ctrl-O - Open File or Project;
Ctrl-F / Ctrl-H - Find and Replace text within a Document, a Project or in all open Tabs;
Ctrl-Z / Ctrl-Y - Undo/Redo;
Ctrl-A - Select All;
Ctrl-C/Ctrl-V/Ctrl-X - Copy/Paste/Cut;

Less popular, popular but very handy:
Ctrl-Shift-U / Ctrl-Shift-L - Change selected text to upper case or lower case;
Ctrl-K+Ctrl-C / Ctrl-K+Ctrl-U - Comment/Un-comment selected text;
Alt-Break - Cancel Query execution;
Ctrl-L - Generate Estimated Query Execution Plan;
Ctrl-M - Turn ON functionality to Produce Actual Query Execution Plan;
Ctrl-T / Ctrl-Shift-F / Ctrl-D - Switch between output to Text, File or Grid;
Ctrl-J - List Members from IntelliSense;
Ctrl-F5 - Parse selected Query or All queries within the current Tab;
Ctrl-F6 / Ctrl-Shift-F6 - Cycle between Code, Grid Result and Messages;

Undocumented/Incorrectly Documented:
Ctrl-U - Brings active cursor to database selection;

If you fill like I missed something interesting, please let me know it to include in the list.

Native (not-changeable) SSMS Query Shortcuts.

That category includes only 3 Shortcuts, which run 3 stored procedures:

Alt-F1 - sp_help
- With no parameters returns two data sets: List of all objects with schema and type and User Defined Data types.
That list has a huge problem, it does not report actual objects' schemas and Microsoft is not going to fix it (See budg report:
- If you select a particular object it will return minimum information about the object, such as "Creation Time". Exception is only Tables, info about Tables is awesome.

Ctrl-1 - sp_who
-  With no parameters returns very limited list of currently connected SQL Server sessions
- With selected "Login Name", returns only list of connections for that "Login Name"
I'd rather use sp_who2, it returns little bit more info.

Ctrl-2 - sp_lock
-  With no parameters returns very limited list of current locks in SQL Server.
- Accepts up to two Session IDs as parameters to filter on.

Not Native (Changeable) SSMS Query Shortcuts.

In this section I will list shortcuts which I use in my SSMS Studio.
You can download scripts from here: SSMS Query Shortcut Scripts
* These scripts are mostly developed by bright minds like: Glen Berry, Paul Randal, Adam Machanic, Ben NAvarez and others, but usually reverted from their original versions.
** Some scripts are developed by myself and I can accep all blame on them.
*** These query shortcuts are live and breathing. I'm constantly add new features, fix old bugs and adjust their functionality.

Here is their description:

Ctrl-F1 - Tables

Procedure "Ctrl-F1" returns following, depending on parameters:
1. No parameters: List of all user tables in current databases sorted by size and number of records.
    It is normal that tables with less records are bigger in size.
2. "Name of a table": "LIMITED" statistics for all indexes on that table.
    For large tables that operation might run for several minutes.
3. "Name of an index": "LIMITED" statistics for that index along with list of Page allocation.
4. "Name of a table or an index" + statistic mode: SAMPLED or DETAILED.
Example 1: tbl_MyTable
Example 2: 'CLUIX_MyTable','DETAILED'

Ctrl-3 - Locks (Similar to sp_lock)

Procedure "Ctrl-3" returns following, depending on parameters:
1. No parameters: List of all current Locks. Similar to sp_lock with some extentions.
2. "Database ID": List of all current Locks for the specified Database.
3. "Session ID": List of all current Locks for the specified session.
 That is possible that "Session ID" and "Database ID" cross each other and wxtra results are returned.
4. "Database Name": List of all current Locks for the specified Database.
5. "Table Name": List of all current Locks on that table.
    Have to be a table in the current database or with fully qualified name.
6. "Object Id": List of all current Locks on that object.
7. "TAB", "PAG", "DB": List of all current Locks for specified type of an object.
8. "X", "U", etc. : List of all current Locks with specified mode.
9. "IP" or "Host Name" or "Login Name" or "Application Name": List of all current Locks for specified source.
 That option might match table or database name.
Example 1: tbl_MyTable
Example 2: TAB
Example 3: 'Sch-M'
Example 4: 123456789

Ctrl-4 - Indexes

Function Ctrl-4: SQL Server Index Troubleshooting.
1. No options: Returns Lists of TOP 10 Not-Used Indexes and TOP 10 Missing Indexes.
   Disclosure A: Both lists are valid only after full regular business workload cycle. Without SQL Server Reboots and Index Maintenance.
   Disclosure B: Before applying Missing Indexes, always check if any similar index exist. Maybe you can just simply modify an existing one.
2. Index Name: Returns Index Statistics and general info.

 Ctrl+5 - Databases

Function Ctrl-5: SQL Server Database Details.
1. No options: Returns List of all Databasese and their files with current sizes and used space.
2. Database Name: Returns sizes and used space for specified DB and Temp db. Lists of all DB Settings.

Ctrl+6 - Performance Troubleshooting

Function Ctrl-6 Options: SQL Server Current Activity.
1. No options: Returns List of currently running tasks, list of all user opened trnsactions and Current Waits stats.
2. Letter "B": Returns only running blocking chains.
3. Letter "S": Returns data in simple mode without Quwery Plan.
4. Letter "W": Returns Only Current Waits stats.
5. Letter "W" + Number of seconds : Returns Waits Stats Delta. (Max=999) (Example: "W10" Returns Delta waits within 10 seconds).
6. Letter "M": Concentrate on Memory. Adds Memory allocation per database.
7. Letter "C": Concentrate on CPU Utilization. Adds dm_os_schedulers, dm_os_workers & dm_os_waiting_tasks.

Ctrl+7 - Recent Connection's queries

 Returns information about current sessions.
 (No Parameters) - Returns full list of current sessions.
 Number - filter by Session ID
 IP Address or "<local machine>" - filter by client Net address.

Ctrl+8 - Query Cache

Function Ctrl-8 Options: SQL Server Query Cache.
1. No options: Returns: % of Adhoc Queries; TOP 1000 List of all queries stored in SQL Server Cache and TOP 10 Working time diagram.
2. "X": Query Plan in XML format. (Might generate an error: "XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.")
3. Part of a query: Will search (TOP 1000) for that part of a query within SQL Server Cache.
4. "ORDER BY + field name + ASC/DESC": Adds sorting to the full List of all queries stored in SQL Server Cache. (option #1)
5. sql_hqsh or plan_handle: Returns all plans for particular SQL Query or only one plan for plan_handle

Ctrl+9 - Statistics

Function Ctrl-9 Options: SQL Server Statistics.
1. No options: Returns List of all Statistics in current DB.
2. Table Name: Returns List of all Statistics for specified table (Applicable for SQL Server 2008R2 SP2 or higher).
3. Statistic name: Returns Statistics' Header, Density Vector and Histogram.

Ctrl+0 - Objects

Function Ctrl-0 Options: SQL Server Objects.
1. No options: Returns List of all tables and List all objects in current DB.
    It is normal that tables with less records are bigger in size.
2. Object Name: Object's code or full description.
3. Index/Table name (+ parameter): Gives info about index allocation. Second is reporting mode: SAMPLED or DETAILED.
For system tables requires fully specifired name like: 'sys.objects'.
Does not handle: Synonyms, Defaults, Constraints.
Example 1: tbl_MyTable
Example 2: 'CLUIX_MyTable','DETAILED'

SSMS Query Shortcuts' Implementation

To set these manual shortcuts you have to do following:
1. Press Alt-T + O, which will bring you to SSMS Tools and Options.
2. Go to "Environment -> Keyboard -> Query Shortcuts"

3. Select the shortcut you need and insert the query into second column's cell.

Things to pay attention to:

1. The query script must not have Carriage Return symbol with ASCII code 0x0D. You can put whole script in one line, but it won't be editable well. So, use the PowerShell script included in the package to remove Carriage Return symbol.

2. In order to use newly created or changed shortcut you have to open new Tab in SSMS. All old tabs will not see the change.

3. These scripts create Temporary Stored Procedures and if your user database  has different collation with TempDB database you'll get an error message.

4. If you going to modify my script or write your own, remember the size limit for Query Shortcuts is 32K.

5. The BIGGEST problem of Query Shortcuts that you can't Export/Import them in SSMS.

If you are  going to use Query Shortcutsand also not satisfied by inability to Export/Import them in SSMS please leave your comment in Microsoft Bug Report: "SSMS Query Shortcuts' Import/Export do not work"

Friday, February 5, 2016

SQL Saturday in Baltimore. Pre-Con survey results.

Just in case you do not know: I'm one of co organizers of SQL Saturday in Baltimore, which will take a place in Columbia, MD on April 30st.

SQL Saturday is wonderful Free learning event about SQL Server technologies (you only pay for your own lunch).
Our SQL Saturday officially is in BI edition. That means there will be not only about SQL Server, but also about SSIS, SSAS, SSRS, Hadoop, BigData, PowerBI, Data Warehousing and etc.

If you live in Baltimore-DC Metro Area, Maryland, Northern Virginia, Southern Pennsylvania, Eastern part of West Virginia, Delaware and as part of your job you are touching SQL Server - there is no excuse for you not to visit Columbia, MD on April 30st.
If you can't be there whole day it is OK, you can come only for the session you want to attend.
Unfortunately in this case you'll miss all great sponsors' prizes at the end of the event.

If you are interested in which sessions and speakers will be present at the event you can take a look here:

If you haven't registered yet here is the link:
Do not be late to get to the waiting list and possibly miss the event.

As part of our SQL Saturday we will also do couple of Full Day paid Pre-Con sessions, which will be scheduled for day before: Friday, April 29th in the same Loyola University building in Columbia, MD.

Final decision on who will be speaking at Pre-Con is not made yet, but here I will publish the results of our survey we did on whom people want to speak and what topics do they want to see in Baltimore:

In the nomination of topic the winners are:
1. T-SQL
2. Performance/Internals
That choice of topics will drive the decision on who will be the speakers.

Speaker nomination is much more difficult.
At first list was so big that I couldn't grab the whole diagram at once.
That probably also plaid a bad role in people's decision. 10 people couldn't determine whom they would like to see.

Some people added new names in the list of suggestions, some of which I immediately added to the questionnaire.
Ben Galluzzo and Rob Volk weren't in the list from the very beginning, that would explain their not perfect results.
I also had as suggestions names of Brent Ozar and Grant Fritchey.
Guys, they were my first choices and I had conversations with them way before I've made the survey.
However, their busy life can't accommodate extra traveling to Baltimore.
Maybe next year?

Had a request to get ANYBODY fro SQLSkills. The person who suggested it also read my mind. I've asked them, but they are too busy and ravel only for their own events and to PASS Summit.

Also have suggested: Kevin Boles, Jason Brimhall, Wayne Sheffield, Devin Knight, Marco Russo, Alberto Ferrari, Chris Webb, Kasper De Jonge. Unfortunately it was too late to include all of them to the list and then the list would be tooooooo big.
Another my concern is that those names are not very famous in our area. Besides of Wayne Sheffield none of those SQL speakers recently presented in Baltimore.
I'd suggest to bring those guys to SQL Saturday first, and then the next time people will be more knowledgeable and will vote for the best.

Back to the diagram:
I've already contacted the winners: Itzik Ben-Gan and Adam Machanic, unfortunately they also can't come. As a satisfaction Itzik helped as to get book sponsorship from his publisher and we will have his books as a prize at our event.

From all others we have to choose two speakers:
1. Best for SQL, Internals, and troubleshooting;
2. Best for ETL and SSIS

It is already too late to vote or make suggestions.
I've sent invitations and hope these people will accept them.

As soon we will have Pre-Con speakers I will officially announce them.

If you want to be informed immediately, please register for the SQL Saturday event then you'll get an email as soon as Pre-Con registration will be available.