Yet another SQL Server enthusiast

2017-07-12

A.D.C.E.S. Meetup 2017-07-11

Filed under: Uncategorized — sa @ 8:42 PM

Download files

 

Advertisements

2017-07-10

SQL Server Management Studio v17 – three keyboard shortcuts

Filed under: Uncategorized — sa @ 11:37 PM

1. Line swapping

Keyboard shortcut: Alt + Up / Down (new in SSMS v17)

Description: It swaps current line and above (Up) or bellow (Down) line

Example: Ctrl + Up

Alt   UporDown 

2. Vertical selection

Keyboard shortcut: {Alt + Shift + Up / Down or Shift + Alt + Up / Down or Alt + Mouse Selection} + [Typing chars]

Description: It selects source code on columns

Example: Shift + Alt + Down (x4) + (‘

Alt  Shift   UporDown

3. Clipboard cycling

Keyboard shortcut: Alt + Shift + V or Ctrl + Shift + Ins

Description: It cycling backwards  through selected text in clipboard

Example: FactSales, INSERT, UPDATE and also DELETE were copied (Ctrl + C) into clipboard and then Alt + Shift + V (x4)

Ctrl   Shift   V

2017-06-27

Filegroup restoring and database consistency @ sqlservercentral.com

Filed under: Uncategorized — sa @ 10:16 PM

New article published on sqlservercentral.com: [Filegroup restoring and database consistency]

2017-05-08

RoSQL 2017-05-06 Table Partitioning

Filed under: Uncategorized — sa @ 9:32 AM

 

Download *.sql scripts

2017-05-02

Moving LOB data to another filegroup

Filed under: Uncategorized — sa @ 9:19 AM

Note #1: This post is (more or less) a reminder for me.

Note #2: Original solutions are described here and here.

The problem (and a partial solution)

Sometimes, within a SQL Server database, we have to move data to another filegroup. In such cases, the simplest solution is to use CREATE INDEX statement with the ON NewFileGroup clause and also with DROP_EXISTING = ON thus:

image

This solution is available for both clustered and non-clustered indexes. In this cases, SQL Server will rebuild current index using the new filegroup (ON NewFileGroup). The drawback of this solution is that Database Engine will not move also LOB data (Large OBject data types include: old data types [n]text, image but also the new data types [n]varchar(max), varbinary(max), xml, CLR, geometry, geography, sql_variant) to new filegroup. The [main] reason of this behavior can be seen in CREATE TABLE syntax:

image

The CREATE TABLE statement includes the TEXTIMAGE_ON clause what allows us to define which filegroup should be used to store LOB data. Above solution will move only row data without moving LOB data (pages) into new filegroup. So, LOB data remain within old (TEXTIMAGE_ON) filegroup.

Note #3: For heap tables, one solution is to create a clustered index on the new filegroup and then drop it. After dropping clustered index,  the LOB data remain [stored] within new filegroup.

Final solution

The final solution is to recreate clustered index using a partition scheme. In this case, the behavior of SQL Server Database Engine is to move to target filegroup(s) (defined by partition scheme) both ROW and LOB data pages:

image

In order to create partition scheme we need a partition function. The main difference between original solution and this one is that in this case, I’ve used an empty partition function.

Demo

Initial status of ProductPhoto table (AdventureWorks2014 database):

image

Partition function (empty partition scheme), partition scheme (with all “partitions” assigned to [UserObjects] filegroup) + ProductPhoto table is recreated using the new partition scheme:

image

After these operations, the new status of ProductPhoto table is

image

but current table is partitioned. In order to remove partitioning (partition scheme) of current table the simplest solution is to recreate (again) the main table structure (clustered index) on the new filegroup:

image

And the final status of ProductPhoto table (unpartitioned) is:

image

2016-06-05

Trolling Ora

Filed under: Uncategorized — sa @ 3:08 PM

CREATE DATABASE Scott;

GO

SQL Server on Linux Ubuntu - First preview

SQL Server on Linux Ubuntu – First preview

Source

2016-03-16

ITdevConnect 2016

Filed under: Uncategorized — sa @ 1:11 AM

ITdevConnect

ITDC16

2015-06-22

SELECT {d ‘2015-06-20’} [RoSQL Presentation]

Filed under: sql server — sa @ 6:58 AM

2015-06-21

When a query plan goes bad

Filed under: sql server — sa @ 9:03 PM

Source: http://dbareactions.com/post/122027631923/when-a-query-plan-goes-bad

2015-05-08

How to find execution plans with warnings and missing indexes

Starting with SQL Server 2005, execution plans include information about missing indexes and staring with SQL Server 2012 execution plans could include information regarding few warnings (ex. implicit conversions, no join predicates, ). Because both types of information are included in [estimated] execution plans and these plans are stored as XML data we can use following queries that find execution plans with warnings and with missing indexes for TOP(100) queries sorted by average logical reads:

Code Snippet
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

IF OBJECT_ID('tempdb.dbo.#QueryPlans') IS NOT NULL
    DROP TABLE #QueryPlans;

SELECT    pa.*,
        SUBSTRING(st.[text], (qs.statement_start_offset/2) + 1, 100) AS statement_text,

        qs.execution_count, qs.creation_time, qs.last_execution_time,

        min_elapsed_time    = qs.min_elapsed_time,
        avg_elapsed_time    = CONVERT(NUMERIC(38, 4), qs.total_elapsed_time * 1. / qs.execution_count),
        max_elapsed_time    = qs.max_elapsed_time,

        min_worker_time        = qs.min_worker_time,
        avg_worker_time        = CONVERT(NUMERIC(38, 4), qs.total_worker_time * 1. / qs.execution_count),
        max_worker_time        = qs.max_worker_time,

        min_logical_reads    = qs.min_logical_reads,
        avg_logical_reads    = CONVERT(NUMERIC(38, 4), qs.total_logical_reads * 1. / qs.execution_count),
        max_logical_reads    = qs.max_logical_reads,

        min_logical_writes    = qs.min_logical_writes,
        avg_logical_writes    = CONVERT(NUMERIC(38, 4), qs.total_logical_writes * 1. / qs.execution_count),
        max_logical_writes    = qs.max_logical_writes,

        min_clr_time        = qs.min_clr_time,
        avg_clr_time        = CONVERT(NUMERIC(38, 4), qs.total_clr_time * 1. / qs.execution_count),
        max_clr_time        = qs.max_clr_time,

        min_rows            = qs.min_rows,
        avg_rows            = CONVERT(NUMERIC(38, 4), qs.total_rows * 1. / qs.execution_count),
        max_rows            = qs.max_rows,

        qs.[sql_handle], qs.plan_handle, qs.query_hash, qs.query_plan_hash
INTO    #QueryPlans
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) st
CROSS APPLY (
    SELECT    database_name = DB_NAME(rpvt.[dbid]), object_name = OBJECT_NAME(rpvt.objectid, rpvt.[dbid])
    FROM (
        SELECT    xpa.attribute, int_value = CONVERT(INT, xpa.value)
        FROM    sys.dm_exec_plan_attributes(qs.plan_handle) xpa
        WHERE    xpa.attribute IN (N'dbid', N'objectid')
    ) spvt
    PIVOT( MAX(spvt.int_value) FOR spvt.attribute IN ([dbid], [objectid])) rpvt
) pa
OPTION(RECOMPILE);

WITH XMLNAMESPACES (
    'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
    'http://www.w3.org/2001/XMLSchema' AS xsd,
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT    TOP(100)
        XmlMissingIndexes    = qp.query_plan.query('//MissingIndexes'),
        XmlWarnings            = qp.query_plan.query('//Warnings'),
        XmlExecutionPlan    = qp.query_plan,
        t.*
FROM    #QueryPlans t
OUTER APPLY sys.dm_exec_query_plan(t.plan_handle) qp
WHERE    qp.query_plan.exist('//Warnings') = 1
OR        qp.query_plan.exist('//MissingIndexes') = 1
ORDER BY t.avg_logical_reads DESC — You may change default sort criteria thus: avg_elapsed_time, avg_worker_time, avg_logical_writes, etc.
OPTION(RECOMPILE)

Results:

SQL Server Execution Plans - Warnings - Missing Indexes

You may change default sort criteria to ORDER BY avg_elapsed_time /  avg_worker_time / another column from #QueryPlans table.

Download script

« Previous PageNext Page »

Blog at WordPress.com.