THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Damian Widera

  • Kimberly Tripp is going to visit PLSSUG in January (online meeting)

    Paul Randal has recently proposed that SQLSkills could give sessions on the user groups meetings. I emailed him immediately and - making the long story short - the PLSSUG (Polish SQL Server Users Group) will be hosting Kimberly Tripp on 29 th January at 6PM local time. Please visit the  community webpage 

    Here are some details:

    Stored Procedure Optimization Techniques

    Presenter: Kimberly L. Tripp

    Abstract: Kimberly will discuss stored procedure caching and the potential for performance problems due to a problem known as parameter sniffing. There are some common solutions and while they can drastically improve performance, they can also cause other problems. Sometimes a hybrid solution is needs, from this session, you’ll know the best way to write procedures so that stable requests are cached and unstable requests are not (giving you the most optimal performance without wasting resources).

     

    Bio: Kimberly L. Tripp, President / Founder, SQLskills.com

    Kimberly has been a Microsoft SQL Server MVP and a Microsoft Regional Director for over 10 years; she's an author, consultant, and top-rated speaker having spoken at PASS, TechEd, SQLintersection, and numerous other events around the world. She has over 20 years of experience designing and tuning SQL Server solutions with a focus on optimizing procedural code, indexing strategies, statistics, and partitioning. When she's not tuning SQL servers, she's diving and photographing all sorts of underwater critters such as frogfish with her husband, Paul S. Randal.

    I hope that this is great news.

    And I promise - this will not be the only meeting :).

    More details will follow on the community webpage (as well as information how to participate in that meeting) - http://plssug.org.pl/

    Cheers,

    Damian

      

     

     


  • SQLDay 2015 - should we plan it now? Yes - check out who will join us :)

    SQL Folks, SQL Geeks and others....

    Is it the right time  to plan the next conference when the previous was finished last week? YES!!!! It is the right time!!! The PLSSUG is planning to have the biggest community conference ever. It will start at 11th May (Monday - preconf) and will be till Wednesday, 13th May (at least). The first great news is that Adam Machanic will be with us. So do not wait and register for his preconf and sessions :). Adam will not be the only superstar next year but more details will follow. Remember, that this year Bob Ward, Klaus Aschenbrenner and Alberto Ferrari were the special guests. And do not forget about the local leaders - you should have seen them last week in Lublin. So you know what am I talking about.

    So, stay tuned, vist the PLSSUG page frequently

    Cheers

    Damian 

     

     

  • Conference - ITAD Bielsko Biała

    Good evening

    Today I had a lecture on the academic conference ITAD (http://www.itadbb.pl/). That is amazing that 300  people came and took part in the event. 

    I was talking about the SQLCLR which seems to be my main topic in the last months. That is because I use it over and over again in my projects. The idea was to share best practices to the people who probably start practicing this feature.  Many thanks to the organizers for invitation, congratulations once again - it was really impresive event. Hope to see you next year as I promised you to show you some TSQL stuff that makes SQL Server fly (not cry).

    If you want to see what I was talking about you can download the materials from the drive  . Many thanks to Adam Machanik for help in preparing the demos and sharing knowledge about the SQLCLR 

    Cheers,

    Damian 

  • SQL Day Lite 2014 Lublin - part II - photos

    Hello again

    I am pleased to announce that pictures taken during the conference are available. Just take a look here. Thanks to Kamil Nowiński the photos.

    It is also a good moment to say "thanks" to the PLSSUG (Polish SQL Server Users Group) for the organization. Again - it was fabulous :) Over 100 people were attending the Saturday's conference and also over 45 were with us on Friday during the workshops (preconf). Please visit the PLSSUG site for the recorded sessions (comming soon...)

    The agenda of the conference was as follow:

    Łukasz Grala - SQL Server Analysis Services - Multidimensional vs Tabular

    me - All you wanted to know about the DBCC 

    Grzegorz Stolecki - Reporting Services - the rendering report extensions

    Łukasz Grala - What everybody (not only DBA's) should know about the backup

    Marek Adamczuk & Paweł Potasiński - Windowing Functions

    Maciej Pilecki - Event Notification 

      

    Cheers

    Damian 

  • SQLDAY Lite 2014 - Lublin.

    I had the priviledge to participate in the SQLDayLite 2014 conference that was held in Lublin this week. 
    I had the opportunity to prepare one-day workshop (on Friday) and also gave a speech on Saturday.
    The workshop was not easy :) as it was dedicated to the SQLCLR programming.  So we (me & participants) did a really deep dive into the SQLCLR :). So it was really demo intensive day for all of us :)
    On Saturday I was talking about the DBCC and its usage. I hope people enjoyed that talk especially that  I did show how to make a corruption in the db (for training purposes)

    Cheers,
    Damian 
  • My MVA course about SQL Server 2014 & Azure

    If you are tired today maybe you could take your time and spend the whole weekend with the SQL Server 2014 and Azure?

    The new MVA course is ready :) and waiting for you. Just visit the link http://bit.ly/1tJCTts and enjoy. Watch out - it's in Polish :)

    The topics covered:

    • backup encryption
    • manual backup to Azure storage
    • smart backup :)
    • buffer pool extension
    • SQL Server files integration with Azure storage

    more comming....

  • SQL Server 2014 CU4 is available for downloading

    The SQL 2014 CU4 is available :)

    There are some interesting facts:

    ·         66 issues were solved

    BI\Analysis Services

    10

    BI\Collaborative Insights

    1

    BI\Reporting Services

    11

    Data Quality Services\Cleanse\Export Results

    1

    Integration Services

    1

    Integration Services\Tasks Components

    2

    Integration Services\Tools

    1

    Master Data Services\Client

    1

    Master Data Services\Server

    1

    Master Data Services\Setup

    1

    SQL Connectivity

    1

    SQL Engine

    1

    SQL Engine\Column Stores

    4

    SQL Engine\DB Management

    1

    SQL Engine\Extreme OLTP

    1

    SQL Engine\High Availability and Disaster Recovery

    2

    SQL Engine\Management Services

    1

    SQL Engine\Programmability

    4

    SQL Engine\Query Execution

    2

    SQL Engine\Query Optimizer

    4

    SQL Engine\Replication

    2

    SQL Engine\Search

    1

    SQL Engine\Service Broker

    1

    SQL Engine\Spatial

    1

    SQL Engine\SQLOS

    1

    SQL Engine\Unstructured Data

    1

    SQL Server Client Tools

    3

    SQL Server Client Tools\Database Performance Tools

    1

    SQL Server Client Tools\Powershell

    1

    SQL Server Client Tools\SSMS

    3

     

    ·         The release build is 12.0.2430.0 

    You can find more details on the official page: http://support.microsoft.com/kb/2999197/en-us

    Cheers

    Damian 

     

     

     

  • Transactional replication - how to initialize a subscription from a backup

    Today I had a chance to initialize my scubscription in transactional replication from backup. The database is quite big (120GB) and I thought it I would be better to use this method to start the replication process.

    Unfortunatelly this is not possible from the replication wizard and you have to change the publication and subscription definition manually.

    Here are the steps - if you work with new replication:

    1) Create a publication script and update manually the  sp_addpublication command by changing the @allow_initialize_from_backup parameter  to "true". In my case it was set to "automatic" as I used the wizard to generate the script. I think that it is usefull to set the other parameter: @immediate_sync to "true", too. 

    2) Run the script on the publisher server and then create a backup of the database 

    3) Restore the database on the subscriber.

    4) Go back to the publisher and create a subscription  script but dont run it. Look into it and add these parameters to the sp_addsubscription procedure:

    @sync_type = "initialize with backup" 

    @backupdevicetype = "disk"  (you could add "tape" or "logical" apart from "disk")

    @backupdevicename = "path to the backup"

    when you yse the logical backup device then put the name od this device.

     

    I know that most of you know that a replication can be started using that technique. I worte this blog post in order to not forget about that "feature" in  the future

    Cheers

     

     

     

     

     

  • Swedish SQL Server User Group - my sessions in September

    I had a great privilege to have two sessions in Sweden in September. The first was done in Malmo and the second I gave in Stockholm. 
    The talks were about waits statistics in the SQL Server ("All you should know about waits"). I realized that during 90 minutes I am not able to tell just *ALL* about the wait statistics so I slightly modified the topic to "All IMPORTANT you should know..."
    If you are interested in the presentation deck you can find it here
      
    Thanks to Swedish MVP's and especially to Johan Ahlen,  Steinar Andersen and Lars Utterstrom for inviting me to Sweden
  • Trace flags - TF 7806

    The trace flag 7806 is necessary when a DBA would like to use the dedicated administrator connection (DAC) in the SQL Server Express. The DAC is turned off when you install the SQL Server but it is a good practice to turn it on after the installation is done. The DBA will really need the have access to the unresponsive database server and having DAC active he/she has more chance to do the work. However this feature is not supported in the SQL Server Express edition by default. 

    Microsoft has added a trace flag 7806 to enable this feature in the SQL Server Express.

    As the flag is a global flag we have to turn it on the service level. You should add a parameter –T7806 to the parameter list and restart the service.

    After you did that you are now able to use the DAC in the SQL Server Express. For example you could try to connect using sqlcmd tool:

    Sqlcmd –S localhost\sqlexpress –E –A

    I made an assumption that on your local server there is an instance of the SQL Server Express which is called "sqlexpress" and you  connect to this instance using your Windows credentials

    When you are connected try to run that query:

    SELECT S.session_id FROM sys.tcp_endpoints as E JOIN sys.dm_exec_connections as S

    On E.endpoint_id = S.endpoint_id

    WHERE E.name = ‘Dedicated Admin Connection’

     

    Last remarks – the DAC can be used only by sysadmins

  • Trace flags - TF 1117

    I had a session about trace flags this year on the SQL Day 2014 conference that was held in Wrocław at the end of April. The session topic is important to most of DBA's and the reason I did it was that I sometimes forget about various trace flags :). So I decided to prepare a presentation but I think it is a good idea to write posts about trace flags, too.

    Let's start then - today I will describe the TF 1117. I assume that we all know how to setup a TF using starting parameters or registry or in the session or on the query level. I will always write if a trace flag is local or global to make sure we know how to use it.

    Why do we need this trace flag? Let’s create a test database first. This is quite ordinary database as it has two data files (4 MB each) and a log file that has 1MB. The data files are able to expand by 1 MB and the log file grows by 10%:

    USE [master]

    GO

    CREATE DATABASE [TF1117]

     ON  PRIMARY

    ( NAME = N'TF1117',

         FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TF1117.mdf' ,

         SIZE = 4096KB ,

         MAXSIZE = UNLIMITED,

         FILEGROWTH = 1024KB

    ),

    ( NAME = N'TF1117_1',

         FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TF1117_1.ndf' ,

         SIZE = 4096KB ,

         MAXSIZE = UNLIMITED,

         FILEGROWTH = 1024KB

    )

     LOG ON

    ( NAME = N'TF1117_log',

         FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TF1117_log.ldf' ,

         SIZE = 1024KB ,

         MAXSIZE = 2048GB ,

         FILEGROWTH = 10%

    )

    GO

    Without the TF 1117 turned on the data files don’t grow all up at once. When a first file is full the SQL Server expands it but the other file is not expanded until is full. Why is that so important? The SQL Server proportional fill algorithm will direct new extent allocations to the file with the most available space so new extents will be written to the file that was just expanded. When the TF 1117 is enabled it will cause all files to auto grow by their specified increment. That means all files will have the same percent of free space so we still have the benefit of evenly distributed IO. The TF 1117 is global flag so it affects all databases on the instance. Of course if a filegroup contains only one file the TF does not have any effect on it.

    Now let’s do a simple test. First let’s create a table in which every row will fit to a single page: The table definition is pretty simple as it has two integer columns and one character column of fixed size 8000 bytes:

    create table TF1117Tab

    (

         col1 int,

         col2 int,

         col3 char (8000)

    )

    go

    Now I load some data to the table to make sure that one of the data file must grow:

    declare @i int

    select @i = 1

    while (@i < 800)

    begin

          insert into TF1117Tab  values (@i, @i+1000, 'hello')

           select @i= @i + 1

    end

    I can check the actual file size in the sys.database_files DMV:

    SELECT name, (size*8)/1024 'Size in MB'

    FROM sys.database_files 

    GO

     

    As you can see only the first data file was  expanded and the other has still the initial size:

     

    name                  Size in MB

    --------------------- -----------

    TF1117                5

    TF1117_log            1

    TF1117_1              4

    There is also other methods of looking at the events of file autogrows. One possibility is to create an Extended Events session and the other is to look into the default trace file:

     

     

    DECLARE @path NVARCHAR(260);

    SELECT    @path = REVERSE(SUBSTRING(REVERSE([path]),

             CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'

    FROM    sys.traces

    WHERE   is_default = 1;

    SELECT    DatabaseName,  

                  [FileName],  

                  SPID,  

                  Duration,  

                  StartTime,  

                  EndTime,  

                  FileType =

                            CASE EventClass       

                                 WHEN 92 THEN 'Data'      

                                 WHEN 93 THEN 'Log'  

              END

    FROM sys.fn_trace_gettable(@path, DEFAULT)

    WHERE   EventClass IN (92,93)

    AND StartTime >'2014-07-12' AND DatabaseName = N'TF1117'

    ORDER BY   StartTime DESC;

     

    After running the query I can see the file was expanded and how long did the process take which might be useful from the performance perspective.

     

     

    Now it’s time to turn on the flag 1117.

    DBCC TRACEON(1117)

     

    I dropped the database and recreated it once again. Then I ran the queries and observed the results. After loading the records I see that both files were evenly expanded:

    name                  Size in MB

    --------------------- -----------

    TF1117                5

    TF1117_log            1

    TF1117_1              5

    I found also information in the default trace. The query returned three rows. The last one is connected to my first experiment when the TF was turned off.  The two rows shows that first file was expanded by 1MB and right after that operation the second file was expanded, too. This is what is this TF all about J

     

  • Memories about Tadeusz Golonka

    Today at 10:55 AM, Tadeusz Golonka - my greatest  Mentor and Teacher  passed away. I had te opportunity to met Tadek in person several times last years. It was always a great experience to see how he shared his energy and passion. I was always impressed and had a lot of new ideas after such meeting or lecture.

    I can remember the meeting  in early 2009 and his briliant speech he did for us, the MVP community in Poland. We spent two days together and he talked to us all the time. He gave us examples how to share IT passion to other people and how to be better person for others. He was the greates Mentor I have ever met - I realized this during that meeting. My greates dream was and still is to be "like Tadek". Many Times I just went to events to see / hear him on stage ("in action"). I always wanted to have his energy, empathy and passion. Now I have to live without his good words and advices....

    Let me put here the words that Adam Cogan wrote on Tadek's profile on Facebook. I just can't write about that fatal accident. 

    "The circumstances of Tadeusz Golonka death are too tragic. Tad stood up to offer his seat to an elderly lady, he lost his balance and then he slipped and hit the tram door hard. He then fell out of the tram and hit the metal barriers that separate the tram rails from the street. It was a severe accident....

    .. So horrible.  At first it was a miracle is that he survived... he fought for several days.  My thoughts are with his lovely family. The family have asked for blood donations as a symbolic gift. Tad received a lot of blood.  Thank you Tad, you were a wonderful person. I will remember you as a kind man, a gentleman. "

    RIP Tadeusz- You will never ever be forgotten. You are with us all the time

     


     

  • Annual SQL Server conference in Poland - SQLDay 2014

    We had a great 3-days conference this year in Poland. The SQLDay (7th edition) is an annual community conference. We started in 2008 as a part of C2C (community to communities) conference and after that, from 2009 the SQLDay is the independent event dedicated to the SQL Server specialists. 

    This year we had almost 300 people and speakers like Bob Ward, Klaus Aschenbrenner and Alberto Ferrari. Of course there were also many local Polish leaders (MVP's and an MCM :) )

    If you are curious how we played in Wroclaw this year - just visit the link http://goo.gl/cgNzDl (or try that one https://plus.google.com/photos/100738200012412193487/albums/6010410545898180113?authkey=CITqmqmkrKK8Tw)

     

    Visit the conference site: http://conference.plssug.org.pl/ 

  • In-Memory OLTP Sample for SQL Server 2014 RTM

    I have just found a very good resource about Hekaton (In-memory OLTP feature in the SQL Server 2014).

    On the Codeplex site you can find the newest Hekaton samples - https://msftdbprodsamples.codeplex.com/releases/view/114491.

    The latest samples we have were related to the CTP2 version but the newest will work with the RTM version.

    There are some issues fixed you might find if you tried to run the previous samples on the RTM version:

    Update (Apr 28, 2014): Fixed an issue where the isolation level for sample stored procedures demonstrating integrity checks was too low. The transaction isolation level for the following stored procedures was updated: Sales.uspInsertSpecialOfferProductinmem, Sales.uspDeleteSpecialOfferinmem, Production.uspInsertProductinmem, and Production.uspDeleteProductinmem.

     

  • SQL Server 2014 has come into the light - the RTM will be available on 1st April

More Posts Next page »
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement