Index Rebuild VS Reorganize

Index Rebuild VS Reorganize

What is the difference between Index rebuild(alter index .. Rebuild) and index reorganize(alter index .. reorganize)- earlier version of SQL Server 2000 or earlier called it as defrag… it’s a basic blog and will not cover online rebuild and column store index.

Index Rebuild index re-organize
leaf level data pages gets re-create so data page number will change it re-arranges data in data pages so page number of leaf level will not change
Its all or Non so when you start it it has to complete or has to rollback, so be careful while it runs as you cannot stop it.and pages will be locked so please do not run it in production hrs. Stop and go, if you stop it it will start next time where it was stopped.
it will take same amount time irrespective of fragmentation at every run Time of execution will vary depends upon the fragmentation that’s the one of the reason Microsoft has suggested the bench mark of 30%.
update statistics with FULL SCAN whenever rebuild runs it will not update statistics. you have to run statistics manually after reorg
Index Rebuild is the not so fully logged(it will minimally logged in bulked-log recovery model) Index re-organize is fully logged irrespective of recovery model, so please make sure that you monitor/manage tlog size carefully when you reorganize the index
Multi threading process so faster its single threading process

This is what I can think of at this time but would like to keep this blog updated.

Could see good blog by saurabh Sinha on net.

http://saurabhsinhainblogs.blogspot.in/2013/05/difference-between-reindexing-and.html

 

 

 

Posted in Basic, Index, Others, Troubleshooting | Tagged | Leave a comment

SQL Server 2016 – lets see

Hey friends.

I am here again with what’s new blog … things are changing quite frequently and requirement is also changing. To fulfill the requirement system has to catch-up to the mark.

As you know it’s a BI world and with lots of lots of data around and we would like to access or speed up the system accordingly and globalization is so high has to integrated with other technology/vendors is also important.

I know I am not getting time to write much blog and that’s the reason could not finish whats new in 2014 in detail. But now when I am learning sql 2016 looks like things are changing dynamically and have so much new coming in with the add-on and enhancement of the existing one.

Initially when I was studding 2014 was which was having much focus on in-memory (Hekaton) which was the major new feature for it, and other features like

Buffer Pool expansion and major change in carnality estimation, Delay durability, incremental statistics priority waits for online operations.

Now on SQL Server 2016 it another great product with lots of improved and stabilized product I would say and I believe as things has come so quickly and system is let to migrate to new system it would be possible that you could see management would like to adopt SQL 2016 rather then 2014 as it includes all feature of 2014 with more stability (my view).when we think about to move out of SQL 2005(premier support will expire in Aug 2016(hope so this time)).

When talk about SQL Server 2016 it’s another great product of Microsoft team and great achievement by experts. We could see some of the great new features and add-on enhancement of the existing features to make product more powerful.

Following are some of the new features SQL Serve 2016 offers includes:

  1. QUERY store
  2. StrechDB :to cloud
  3. Temporal table (view historical data)
  4. In Memory enhancements
  5. support of R Language
  6. Always encrepted (SSIS)
  7. PolyBase (connection to HaDOOP)
  8. Row-level security

I would explore each in coming blogs

 

Posted in Others, sql 2016, What I learned today, Whats New | Tagged , | Leave a comment

installation SQL Server 2016

here is some of the installation(https://thakurvinay.wordpress.com/category/installation/)

very nice step-by -step installation  process for SQL Server 2016:

http://www.databasejournal.com/features/mssql/installing-sql-server-2016.html

Posted in Installation | Tagged | Leave a comment

log reader uses single CPU

For replication even if you have multi core CPU it uses only one as log reader agent has single threaded operation it will use only one threaded only.

Posted in Replication, What I learned today | Tagged | Leave a comment

Whats new in SQL Server 2016

While learning SQL Server 2014. looks like we need to swing on it as SQL Server 2016 is coming with great new advance features and stable SQL Server(just like after 2005 to SQL Server 2008).

To learn more about SQL Server 2016 whats new Microsoft has launch a new site call docs.com which will have quite a great videos and information.

https://docs.com

which will contains all the videos about whats new in SQL Server 2016.

http://sqlblog.com/blogs/sergio_govoni/archive/2015/07/28/sql-server-2016-video-pills.aspx

just like Microsoft videos channel 9.

Happy Learning.

reference:

http://sqlblog.com/blogs/sergio_govoni/archive/2015/07/28/sql-server-2016-video-pills.aspx

http://www.sqlservercentral.com/articles/SQL+Server+2016/127518/

 

Posted in Free Stuff, sql 2016, Webcast, What I learned today, Whats New | Tagged , , | Leave a comment

hey its time for 2016!!!

Hi all

Yeah its true microsoft announced SQL server 2016 cp2 has been released to public and also we can read books online for it.

 http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/default.aspx

First blog from mobile …..happy leaning…

 

 

Posted in sql 2016, What I learned today, Whats New | Tagged , | Leave a comment

Whats New 2014

Hey all,

Continue to my whats New series,  I will try to write blog a day for whats new is 2014 as well. I am going through its new features and the more I am reading it it impresses me a lot. and I am feeling that this is it. I really like it.

As you know, I am continue to writing blog a day series for and Whats new series from SQL Server 7, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. Now its time for SQL Server 2014(I know 2016 is coming on its way). You can get all my Whats New blog @ What’s New Tag(hope this will goes wellJ).

Following are some of the features which I may go much dipper on but I think I need some time to do so.

Initially When I heart about 2014 I though it has on in-Memory as a new feature later realize that it has much more(will try to add more during my learning):

sql server 2014:”
1 >> Memory-optimized tables (formerly known as Hekaton) :are available in Enterprise editions, as well as evlauation and developer editions. In-Memory OLTP is a high performance, memory-optimized engine
http://msdn.microsoft.com/en-us/library/dn673538.aspx
http://blogs.msdn.com/b/saponsqlserver/archive/2014/02/09/new-functionality-in-sql-server-2014-part5-in-memory-oltp.aspx
2 >>Memory-Optimized Table checkpoint:The automatic checkpoint for memory-optimized tables runs after the log increases 512MB after the previous checkpoint.

3 >>Buffer Pool Extension :Enterprise/ Business Intelligence/ Standard(64 bit)
Buffer Pool Extension Edition:The Buffer Pool Extension feature is supported in Enterprise, standard, Evaluation , and Developer editions only.
The Buffer Pool Extension (BPE) feature of SQL Server 2014 allows SSDs to be used as additional buffer pool memory.
4>> Database Backup Encryption :
Starting in SQL Server 2014, SQL Server has the ability to encrypt the data while creating a backup.

http://msdn.microsoft.com/en-us/library/dn756429.aspx

5 >> Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator  (trace flag 9481 )setting trace flag 9481
The first major redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0.  The goal for the redesign was to improve accuracy, consistency and supportability of key areas within the cardinality estimation process,

XML showplan text
http://msdn.microsoft.com/en-us/library/dn673537.aspx

Select TOP (tie/percent) gives worse performance :resolves using new Cardinality Estimator
http://sqlblog.com/blogs/hugo_kornelis/archive/2014/11/06/how-top-wrecks-performance-part-1.aspx

6 >>SQL Server Backup to URL

SQL Server Backup to URL was introduced in SQL Server 2012 SP1 CU2 supported only by Transact-SQL, PowerShell and SMO. In SQL Server 2014 you can use SQL Server Management Studio to backup to or restore from Windows Azure Blob storage service

7>> The 90 compatibility level is not valid in SQL Server 2014(120)

8>>We can define an Availability Group replica that resides in Azure.

9 >> Updateable columnstore indexes: This means you no longer have to drop and re-create columnstore indexes every time you need to load your warehouse tables

10>> Resource Governor for I/O : set the minimum and maximum reads or writes per second allowed by a process in a disk volume.

11 >>Delayed durability : Delayed durability allows you to return control back to the application before the log is hardened
Under delayed durability, log blocks are only flushed to disk when they reach their maximum size of 60KB.
the facility exists to make the delayed durability choice per transaction too
http://www.sqlskills.com/blogs/paul/delayed-durability-sql-server-2014/

12 >>Incremental statistics: Incremental statistics in SQL Server 2014 allow you to update just those rows that have changed and merge them with what’s already there.

13 >>  Low Priority Wait /Low priority of online operations :you can specify how your re-index operation will handle being blocked. You specify how long it will wait and what to do when the wait is over. Will you have it follow traditional behavior and wait indefinitely? Will you have it terminate and move to the next table? Or will you kill the blocking query, so your re-indexing can complete? It’s your choice.

14>>parallel insert functionality of SELECT INTO :

http://msdn.microsoft.com/en-us/library/cc645993.aspx#High_availability
http://www.infoworld.com/article/2606990/database/147960-10-best-new-features-in-SQL-Server-2014.html
http://blogs.msdn.com/b/saponsqlserver/
http://blogs.msdn.com/b/saponsqlserver/archive/2014/01/16/new-functionality-in-sql-server-2014-part-2-new-cardinality-estimation.aspx

Posted in Sql server 2014, Whats New | Tagged , | Leave a comment

re-initialize replication not working

for someone re-initialization is very simple, just go to publisher and right click and select re-initiallize and it will ask for do you what to generate snapshot now, if you say “yes” it will create a metadata of the articles accotiated with that publisher at snapshot specified location(if its at distributor -would be good). and snapshot agent runs and done. starts appling that snapshot at subsriber which inturns delete the tables and re-create.

but what if you run it several time but nothing happens and you dont know whats going on, as it dont provide you the error any where and also it will not show where is it going and what it is doing. for you its just like nothing happening.

so what will you do, as we are re-initializing the subsriber so we are safe to delete the subsriber and add the new one which inturns does the same of initialization. so I did so, guess what…. it started working and surprisingly I get error at the replication stating

cannot drop the table ‘xxx’ as its it is being referenced by object ‘yyy’

so now what to do, if you see the object ‘yyy’ its a view with schema binding option enable, due to which table cannot be drop.

this could be also because of function is using that articles(objects). so if  you see re-initialize is not working first check this dependent /reference objects with respect to replicated articles.

This may save some time for some one…

 

 

Posted in Add/remove, High Avaliability, Replication, Troubleshooting | 1 Comment

Sql Cluster Resource not comming online

when you check on the sql server services and sql agent services its getting started on windows cluster but on cluser its going down again. on error log you may get the cluster service

Login failed for user ‘cluster service account’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

first thing you might think would be check the account password and see if its working– yeah its working now what next.

read the error log carefully and try to understand what is happening. here you could see sql server is started but when it tryed to attach – make available to cluster with cluster service acocunt its getting failed. means the given service account might not have previlage to sql server.

so what we did, we made cluster/sql fool and made a work aournd as failed over the cluster and as soon as sql server started, we open the sql server and checked is the given service account has access to it, and we were right, that account were not existed on to sql server, we have added that accound and granted sysadmin role and you know what…. it WORKS !!!

the possiblility of this issue could be:

“The cluster service account has been changed and that has not been updated at sql level”

Thanks to my frined Nitin here who helped me to resolve this issue.

Posted in Add/remove, Bug, Disaster Recovery, Error Log, High Avaliability | Tagged , , , , | Leave a comment

TempDB Contention 2.1.%

Problem:

When you are performing many DML operations and makes tempdb contesnion on it, You may get wait_resource of  2.1.103

check perfomance counter:

  • Temp Tables Creation Rate
  • Temp Tables For Destruction

When you are performing many DML operations and makes tempdb contesnion on it, You may get wait_resource of  2.1.1 and 2.1.3

Sys,Dm_os_waiting_task – resource like (2:1:%) where % 1:PFS and 3 GAMs contention issue and 103 is DDL contension on Create/Drop temp table on sysmultiobjrefs objects.

Solution:

for 2.1.1 and 2.1.3:

>> check tempdb is on seperate LUN (drives)

>>Enable the trace flag -T1118

>>check the Sort and DML queries on temp objects and try to tune it.

for 2.1.103:

>> enable trace flag -T1118

>> try not us create temp objects.

>> try to include indexes on the temp table.

>> apply hotfix

Must read :

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

http://www.mattwrock.com/post/2011/09/10/Latch-waits-on-21103-You-are-probably-creating-too-many-temp-tables-in-Sql-Server.aspx

http://michaeljswart.com/2013/01/pagelatch-contention-on-21103/

 

 

 

Posted in DB Design, DMV, Internal, Lock/Blocking, Performance Tuning, TempDB, Troubleshooting | Leave a comment