Database Snapshots in Index Rebuilding

 

Yesterday I posted a blog about database snapshot and this is somewhat an extension to it. Yesterday we checked the behavior of a database snapshot with database backups. Now let us check it’s relation with Indexes.

Let us create a database and relevant table along with indexes.

Then I manually insert data so that my indexes are fragmented which is shown below.

Then I created snapshot and as yesterday verified the file sizes.

Then did a index rebuild and verified the file sizes again.

So the sizes are increased which means index rebuild does effect the database snapshots.

Database Snapshot, is it only for Data Modifications?

Database Snapshot consists of a read-only static view of the database without including the uncommitted transactions.

If I put all of the above stuff in one picture, it will be like this.

In simple terms, when we create a database snapshot, empty spare file will be created As and when data is changed in the database, data pages are moved to the sparse file. So if you are reading from the snapshot, modified data pages will be read from the sparse file while non-modified file pages are read from the source database.

Now the question is, Is it only the data modifications are sent to the sparse file.

Let us examine this.

 

Let me create a database and some sample data for it.

 

Then I will take a full back and update few data as shown below.

Now then, back to the business end.

Let us create a database snapshot.

After creating this let us see the size of the snap shot file size.

So the file size of the snapshot file is 3.06 MB but size on the disk is 128KB which is actual data size.

Now let me take another backup.

Let us see the file size again.

What! how come, I didn’t do any data changes, still the size on disk has increased more than twice without any data modification.

Explanation

When there are data changes after a full backup, it will set a bit value in page so that for differential backup it can identify which pages need to taken into the differential backup. This value will be reset with a full backup. Since you have done a full backup after the database snapshot creation, full backup will modify the data page. So database snapshot is not only for the data modification but anything changes to the data page will move the page to the database snapshot.

Anything-SQL Lightning Talks - 2nd Edition

The SS SLUG is organizing the 2nd Edition of Anything-SQL Lightning Talks for the August 2012 meet-up. This is where a group of presenters will speak for 5 minutes on any topic related to SQL Server. We are signing up people for this session and would like you to talk as part of it. Interested? Want more information? Just click here...

SS SLUG August 2012 Meet-up

We are planning for the August 2012 SS SLUG meet-up to be held at Pearson Lanka, in Orion City - Dematagoda, happening on August 15th. We shall provide more information on that soon.

SQL Server 2008 R2 SP2 Released

Customers are highly encouraged to stay on a supported service pack to ensure they are on the latest and most secure version of SQL Server 2008 R2.  To obtain SQL Server 2008 R2 SP2 with its improved security and supportability please visit the links below:

·        Download SQL Server 2008 R2 SP2

·        Download SQL Server 2008 R2 SP2 Express

·        Download SQL Server 2008 R2 SP2 Feature Pack

Oracle Unveils Migration Tool for Microsoft SQL Server to MySQL

Oracle is going after users of Microsoft's SQL Server with a new tool for migrating data from SQL Server to its own MySQL database, the vendor announced Wednesday.

The tool has been built into Oracle's MySQL Workbench administration console, and with it, applications written for SQL Server can be easily tweaked for MySQL, Oracle said in a statement.

Oracle is also releasing an improved version of the MySQL Installer for Windows environments, as well as a new MySQL Notifier for Windows tool that "helps developers and DBAs to easily monitor, start and stop their MySQL database instances, with the Microsoft SQL Server look and feel," Oracle said.

In addition, Oracle is offering a new plug-in that allows users with no experience on MySQL to work with MySQL data inside Microsoft Excel.

Oracle claims that MySQL users can experience up to 90% less total cost of ownership compared to running SQL Server 2012.

More at:

http://www.oracle.com/us/corporate/press/1715479

http://www.computerworld.com/s/article/9229646/Oracle_hopes_to_poach_Microsoft_SQL_Server_users_with_MySQL_migration_tool?taxonomyId=18

Find Orphaned Users In SQL Server

Orphan users can occur once you detach databases or restore a database from another SQL Server database instance. Read this FAQ how to manage those instances.

Database Tuning Advisor (DTA) Naming Conventions

DBAs are using Database Tuning Advisor a.k.a. DTA get recommendations for better performance of their databases.

Following is the index script created from the DTA.

in this index name is _dta_index_Contact_13_341576255__K1_4_6 and people might think that this does not have any standards, but it does have a standard.

You have four parts in this index naming.

A

This is straight forward. It is the object name or the table name.

B

13 is the database id for the Adventureworks.

C

Object Id of the contact.

D

Columns order of the key columns.

Restoring to Existing Database

If you want to restore to the existing databases, first you need to make sure that database is not accessible by any other users. So you need to change the database into single user mode before restoring.

However, in SQL Server 2012 you have this option inbuilt with the restoring option.

Cache Plans for Estimated Queries

Let me put this in simple. Yes when you click estimated query plan as shown in the below image it will cache the query plan.

Let us verify this.

Let us clear the cache first and write the query first and then if you click the Display Estimated Execution Plan button

Then if you examine the cache plan with following query.

you can see that query is cached. This is fixed in SQL Server 2012.

Enhanced Features in Database Restore in SQL Server 2012

New article on Enhanced Features in Database Restore in SQL Server 2012

Increasing the LOG file size

I was doing some research into VLF and was trying increasing the LOG files with different sizes.

I came across with wired situation where when ever I crease the log size by 4GB, it increases with no time, but I go with less size it takes few or more seconds.

Here is the test.

Simply I created the database with 1 GB log file.

Then I expand the log file with 1-7 GBs and every time I re-create the database.

Here are the results.

It is almost a straight line except for 4 GB.

Let us see the physical log growth at each instances.

So surely there is something wrong when you are incrementing by 4GB. When searching I found the blog post from Paul where he confirms that this is a BUG.

I confirmed that this is resolved in SQL Server 2012.

Is Truncate a DDL Statement or DML Statement?

Since operational wise or user experience wise, truncate is equal to DELETE entire table and DELETE is a DML statement, most people think that Truncate is DML statement.

So let us verify this.

Let us create a table to play around.

Let me create user with DML permissions.

Now using this user let us truncate the employee table.

You will end up with an error as shown below.

Msg 1088, Level 16, State 7, Line 2
Cannot find the object "Employee" because it does not exist or you do not have permissions.

You will end up with an error as shown below.

Now let us create a user with DDL permission.

Let us truncate the table using this user.

since this is a success Truncate is a DDL statement.

Scalable String Storage

This is a new storage method introduced for dimension attributes and distinct count measures in SSAS Multidimensional Online Analytical Processing (MLOAP) engine. In previous versions of SQL Server, the string storage file is not possible to grow over 4 GB. With SQL Server 2012 version you have a slight flexibility with this where you have the luxury of configuring it so that you can have a string storage file of more than 4GB.

 

String Store

In an SSAS multidimensional database, strings are stored separately from numeric data. This architecture is to allow for better performance of characteristics of the data. String data is usually found in dimension attributes which have names or descriptions etc. Apart from those string values, it is also possible to have string data in distinct count measures. As said before, in the previous versions for these String Store files, there is upper limit of 4 GB. Whenever, you hit the limit, file system error will be generated and your CUBE processing will be halted.

 

Resolution in SQL Server 2012

In SQL Server 2012, you have a solution where you can set the storage option for a dimension.

· Using SQL Server Data Tools (new tool as a replacement for SQL Server Business Intelligence Development Studio (SSBIDS)) open the project that contains the dimensions or measure groups which you want to adjust to eliminate the error.

· In Solution Explorer, double-click the dimension.

· In Dimension Designer get the Attributes pane by pressing F4.

· Select the parent node of the dimension. If the dimension is Product, select Product and not one of the child attributes like English Product Name or Class etc.

In the Properties pane, in the Advanced section, set StringStoresCompatibilityLevel to 1100 which is 1050 now.

You can set this option from the Cube portioning option as well.

So this configuration is only for dimension wise and not for the cube level which means you can set your configuration to the only required dimensions or measure groups.

This storage configuration will apply once you process your cube only.

 

StringStoresCompatibilityLevel

So though you can type any value here only meaningful value will be 1050 or 1100. Not sure why it was not given in a drop down so that users will not have to remember these numbers and drop down will make users not to make any mistakes.

As 1050 is the default value, when you have that value string storage has the maximum String Store file limit of 4 GB.

When it is set to 1100 does not mean that you have no limits but the limit changes to 4 billion unique strings per store. Still you have an upper limit.

Important Notes

Storage is a concerning factor in 1100 compatibility level. Following is the comparison of file sizes of String Store files of two SSAS cubes; one cube is with 1050 compatibility level while the other cube has compatibility of 1100.

For the following example, two cubes were created using DimCurrent, DimCustomer, DimDate, DimProduct, DimPromotion, DimSaleTerritory and FactIntetSales in AdventureWorksDWDenali database with different StringStoresCompatibilityLevel.

Storage sizes of each dimension are measured and increment is also calculated.

 

Dimension	StringStoresCompatibilityLevel	Increase
1050	1100
Currency	216 KB	488 KB	126%
Customer	9.71 MB	12.0 MB	23.6%
Date	1.66 MB	3.01 MB	81.3%
Product	2.44 MB	4.64 MB	90.1%
Promotion	1.02 MB	2.15 MB	110%
SaleTerritoty	344 KB	752 KB	118.6%
FactInternetSales	8.10 MB	9.28 MB	14.5%

So you can clearly see that compatibility level 1100 needs more storage. Another point to remember is, you will not be able to revert back to the 1050 compatibility level. So plan carefully before converting it from 1050 to 1100 and make sure you select the only necessary dimensions to have 1100 for the StringStoresCompatibilityLevel.

SQL Mail is discontinued in SQL Server 2012

So now time has come to move to database mail. With this change you will not see following SQL Mail related system procedures in SQL Server 2012.

• · sys.xp_deletemail
• · sys.xp_findnextmsg
• · sys.xp_get_mapi_default_profile
• · sys.xp_get_mapi_profiles
• · sys.xp_readmail
• · sys.xp_sendmail
• · sys.xp_startmail
• · sys.xp_stopmail
• · sys.xp_test_mapi_profile
• · sys.sp_processmail

[SS SLUG] July 2012 Meet-up

Session #1
Do It Right: Database Development and Design Considerations
SPEAKERS: Dinesh Priyankara (MVP) and Gogula Aryalingam (MVP)

Session #2
Breaking a Myth in 300 Seconds: Full Backup is required to restore a broken log backup chain
SPEAKER: Dinesh Asanka (MVP)

Location: Microsoft Sri Lanka, Level 11, DHPL Building, No. 42, Nawam Mawatha, Colombo 2, SRI LANKA
Date: Wed, July 18, 2012
Time: 06:00PM Onwards
Entrance: ABSOLUTELY FREE

More information: http://www.sqlserveruniverse.com/ssslug.aspx

Strange Behavior for Table Variable with Indexes

I was trying some research on table variable with indexes and came across with this strange behavior which I can’t explain. If you can let me you are welcome.

Let me clear the cache first so we are clear.

So I created table variable with unique clustered index and insert data into the table variable.

So let us see the query plan.

So it uses, clustered index of the SalesOrderDetail table and the clustered index of the table variable.

Let us view the cashed plans.

So we have two rows.

First one of course, plan for the table variable insert and the second one (This is what I can’t understand) is for Sales.vOrders indexes view.

I am not using any Index view. Other thing is note here is,  if I don’t have the indexes on the table variable. then this cached plan will not appear. Also, I do the simple select on the query which I am using to insert into table variable, then again this cached plan will not appear.

I don’t have a clue do you?

Finding Connections for A Single Database

This FAQ allows you to find connections relevant to one database

Granting Column Wise Permission

You might know that you have the option of allocating permission Column wise. For example, you can GRANT permission to another user so that he can select Name column of the Employee table while does not have permission to select Salary column.

Let us assume with following case.

I have a table ,( let us say , Production.ProductCategory)  I grant select permission for two columns while deny permission for the entire table for a user as shown below.

So the question is , what is the effective rights. Can he select those columns or will he be denied on select permission?

What is the rule for security,

Denial of access always out weights a grant of access, with the exception of the sysadmin role at server.

If you go by the above rule, the user should not be able to access those two columns. But to your surprise, by default user can select those columns.

So what is the concept behind this?

MSDN says,

A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for backward compatibility.

Can you change this.

Yes you can by running following script.

However, you need to restart SQL Server instance after running the above query.

Who is the SQL Server Guest User ?

Do you know what guest user and how it works in SQL Server. Here is the article for it.