Null Value and Their Impact

In a previous project, I was tasked with testing and analyzing a database that displayed an alarming growth rate. It seemed like either growth estimations were off or the application was inserting data more frequently than expected. The database had grown by over 300% in just two years, which was a clear red flag. After a thorough analysis, I pinpointed the issue to a single table. This table was massive, with over 217 columns and more than 50 million records, and the majority of these columns allowed null values. This is where the problem lay. For a detailed examination, please refer to article Common Mistakes in SQL Server Part 3.

Auto Growth and performance concerns

Auto Growth is a convenient feature that allows database files (primary, secondary, and log) to expand automatically when they reach their capacity, without requiring manual intervention.

Configuring Auto Growth can be done in two ways: through SQL Server Management Studio (SSMS) and T-SQL, either in percentages or megabytes. However, it’s essential to highlight a frequently overlooked database configuration. Neglecting this setting can lead to I/O and CPU spikes, as well as physical fragmentation. For a comprehensive exploration of this topic, please consult Common Mistakes in SQL Server Part 4

Implicit Conversion and it’s Detrimental Effects

Implicit Conversion is an occurrence where SQL Server automatically converts data from one data type to another during query execution. This can happen when comparing columns or values of different data types or when utilizing them in joins or expressions. It’s also known as “type coercion” or “type casting.”

Implicit Conversion can result in unexpected performance issues and hinder query optimization. To delve deeper into this subject, please read Common Mistakes in SQL Server Part 5

Significance of Recompiling Stored Procedures and Views

Recompilation is a valuable tool in SQL Server, especially when you want to ensure that your queries are using the most suitable execution plans. It’s crucial to understand the events that trigger recompilation and use it judiciously to balance performance benefits with the associated overhead. Managing high CPU utilization is vital for maintaining the performance and stability of a SQL Server instance. For an in-depth understanding of this topic, I recommend reading Common Mistakes in SQL Server Part 6

Please help me improve by providing the feedback. You can write your feedback in the comment section of each of the blog article.

Thank you.

Hemantgiri Goswami

The post Common Mistakes in SQL Server – Final appeared first on SQL Server DBA Services - INDIA, SQL Server DBA, Consultation, SQL Server Expert, Remote DBA, Hemantgiri S Goswami.

the advantages of recompiling stored procedures and views in SQL Server and underlined the scenarios where recompilation is recommended. Let’s summarize the key points:

1. Caching Execution Plans: Views and stored procedures in SQL Server can store their execution plans in the system table sys.syscacheobjects, which is used to optimize subsequent executions of the same T-SQL statements.
2. Execution Plan: An execution plan is a data structure that specifies how a query will be executed, optimizing the query for performance. It’s generated by the SQL Server engine for each unique query and stored in the cache.
3. Reusing Cached Plans: SQL Server tries to reuse cached execution plans for views and stored procedures. This minimizes the overhead of repeatedly compiling the same T-SQL statements.

When to Recompile: There are specific events that can invalidate the cached execution plan, and recompilation is recommended in these cases. These events include:

• Schema Changes: Altering a table’s structure.
• Index Changes: Altering or dropping indexes.
• Manual Recompile: Using sp_recompile, the ‘with recompile’ option, or OPTION (RECOMPILE) in your T-SQL.
• Data Changes: Large insertions or deletions of data in tables.
• Mixed DML and DDL: Combining Data Manipulation Language (DML) and Data Definition Language (DDL) in a single T-SQL statement.
• SET Option Changes: Modifying the value of SET options can affect the query’s execution plan.

Cost of Recompilation: Recompilation is an expensive operation, as it involves generating a new execution plan. However, it helps ensure that the query uses an up-to-date and valid plan, which can improve performance and accuracy.

In summary, recompilation is a valuable tool in SQL Server when you want to ensure that your queries are using the most appropriate execution plans. It’s important to be aware of the events that can trigger recompilation and to use it judiciously to balance the performance benefits with the associated overhead. Addressing high CPU utilization is essential for maintaining the performance and stability of a SQL Server instance. If you encounter such issues please refer to this article Resolving High CPU usage in SQL Server.

The post Common Mistakes in SQL Server – Part 6 appeared first on SQL Server DBA Services - INDIA, SQL Server DBA, Consultation, SQL Server Expert, Remote DBA, Hemantgiri S Goswami.

What is Implicit Conversion?

Implicit Conversion occurs when SQL Server automatically converts data from one data type to another during query execution. This can happen, for example, when you compare columns or values of different data types or when you use them in joins or expressions. It is also known as type coercion or type casting.

Why Implicit Conversion is considered bad?

Well, when an SQL Server database engine has to convert one data type to another it must have to use some additional resources to complete the task. For example, while it has to convert data types it will use additional CPU, and it can also lead to not-optimal use of indexes

• Performance Overhead: As you mentioned, implicit conversion consumes additional CPU resources. This is because SQL Server has to perform the conversion on the fly, and this can be especially problematic in queries with large datasets.
• Index Usage: Implicit conversion can also prevent SQL Server from efficiently using indexes. If the data types of columns in the WHERE clause don’t match, SQL Server might not be able to utilize available indexes, leading to slower query performance.

How to Fix Implicit Conversion?

That is why, it is of utmost importance to use the same data type while comparing two columns, values, or using joins. This way Implicit Conversion can be avoided.

• Use Explicit Data Type Conversion: Instead of relying on implicit conversion, explicitly convert data types using appropriate functions like CAST or CONVERT. This ensures you control the conversion and optimize it for performance.
• Align Data Types: Ensure that columns, variables, and literals used in comparisons and joins have compatible data types. This will help avoid the need for implicit conversion.
• Review Your Schema: In some cases, it might be necessary to reevaluate your database schema to ensure that data types are aligned correctly, especially for columns commonly used in filters or joins.

It’s essential for database developers and administrators to be aware of Implicit Conversion and proactively address it in their SQL code to avoid performance bottlenecks. For those who are looking to learn more and test it themselves can download the demo script from my GitHub Repository.

The post Common Mistakes in SQL Server – Part 5 appeared first on SQL Server DBA Services - INDIA, SQL Server DBA, Consultation, SQL Server Expert, Remote DBA, Hemantgiri S Goswami.

What is Auto Growth?

Auto Growth is a feature that allows database files (primary, secondary, and log) to expand when the database file becomes full – without manual intervention.

Auto Growth feature is handy when we do not want to increase the size of database files manually. There are two ways you can set auto growth – using SQL Server Management Studio (SSMS hereafter) and T-SQL. Auto Growth can be configured – In Percent and Megabytes.

How Auto Growth can impact us?

Now that we know what the Auto Growth feature in SQL Server is; I’ll describe how this feature can impact us badly if we do not configure this option carefully. Below is the table that will give you an idea of what would happen if the database file reaches its threshold value and it needs to expand!!

See the calculation below when the Auto Growth option is configured within Percentage (file size is in GB):

You will notice that the database growth is exponential when it is configured with a Percentage i.e. 10%.  This is because this is calculated cumulatively ; the value I have used here is 20 times lower than what we used to work on production systems generally.  If we set Auto Growth in Percentage it will occupy our disk space unnecessarily, more over it creates fragmentation. Apart from that, when database files expand, you will notice high CPU spikes and I/O cycle volume.   

What is the best practice for the Auto Growth option?

The best practice is to configure the Auto Growth option in static value. See the sample calculation below:

The database grows in a controlled manner when Auto Growth is configured with the static/fixed value in comparison to Percentage i.e. 10%.  The value I have used here is 20 times lower than what we used to work on production systems generally.   
 

Nowadays storage costs are indeed lower for desktops or home systems but it is very costly when it comes to servers and data centers and this scenario will just add overhead to the cost of storage solutions. So, the best bet is to configure Auto Growth with a static value.

I would advise configuring the Auto Growth option to static value only. If you want to test the scenario, I have uploaded a few scripts on SQLServerCitation GitRepo.

004-1_AutoGrowth

004-2_Audting_AutoGrowth

004-3-FixAutoGrowthSetting

The post Common Mistakes in SQL Server – Part 4 appeared first on SQL Server DBA Services - INDIA, SQL Server DBA, Consultation, SQL Server Expert, Remote DBA, Hemantgiri S Goswami.

What is NULL?

Null is the value which indicates the value is unknown. Null is different than zero or an empty string, as a zero or empty string indicates that the value is known (refer http://en.wikipedia.org/wiki/Null_%28SQL%29 for further reading.)

Why should we avoid NULL values?

In one of my previous projects I was asked to perform some tests and do analysis on a specific database which had a tremendous growth rate, and it seemed that either the estimation related to growth was wrong, or the application inserts data more often than is expected. The database growing more than 300% in just 2 years wasn’t a good sign – something was really wrong. After I finished the analysis I found that there was a single table which had significantly contributed to the database size – that table itself was 450+ gigs in size; it had 217 columns and more than 50 million records, most of the columns allowing null values, which is where the problem was.

There is a misconception that if we have the NULL values in a table it doesn’t occupy storage space. The fact is, a NULL value occupies space – 2 bytes. If we do not consider this point at the estimation stage we will have trouble with the growth of the database. Sometimes, however, application design demands some columns to be there for future use, and in this case, there will be NULL values.

Let me give you an example – create a table “tblEmployee” having thirteen columns of fixed length data type. I will add 150000 records and check the size of table. Remember, we are not creating a primary key/clustered index – thus this will be the Heap.

1. Specify the number of rows that will be present in the table: Num_Rows = 15000
2. Specify the number of fixed length and variable length columns: 13
3. Part of the row, known as the null bitmap, is reserved to manage column nullability:
4. Calculating the variable-length data size:
5. Calculate the total row size:
6. Calculate the number of rows per pages (8096 free bytes per page)
7. Calculate the number of pages required to store all the rows
8. Calculate the amount of space required to store the data in the heap (8192 total bytes per page)

PS: Click here to know why it’s always 2+Value

Num_Rows = 150000
Fixed_data_size= 4+15+15+4+25+25+10+10+3+4+4+35+35 = 189
Num_Variable_Cols = 0
Max_Var_Size = 0
Null_Bitmap=2 + ((13+7)/8) = 4.5 = 4
Variable_Data_size: 0
Row_size = Fixed_data_size + variable_data_size + Null_bitmap + 4
Row_size = 189 +0 + 4 + 4
Row_size = 197
Rows_per_page = 8096 / (122+2) = 40.27 = 40
Num_Pages = Num_rows / Rows_per_page
Num_Pages = 150000 / 40 = 3750
Heap Size (bytes) = 8192 * 3750 = 30720000 Bytes
    

The size of the table tblEmployee is 30776KB which means storing 150,000 records would occupy 30 MB. Now, lets calculate this for 50 million records – you will get 9990 MB ((50000000/150000)*30). Remember we have calculated this for 13 columns only; if your table has more columns the size could be bigger than this.

You may want to download a demo script here to understand it better. The above example shows how your estimation will be wrong if you do not consider NULL values while estimating database size. To estimate your table size correctly, you must consider this point, and you should try to narrow down the possibility of having NULL values in your tables.

Reference: Estimate the size of a table

The post Common Mistakes in SQL Server – Part 3 appeared first on SQL Server DBA Services - INDIA, SQL Server DBA, Consultation, SQL Server Expert, Remote DBA, Hemantgiri S Goswami.

How does it work?

The Identity column property has two components: a seed value and an incremental value. A seed is the initial value  set for that particular column , where as an incremental is added to last inserted value when a new row is created. When we set the Identity properties, we have to define the value for first record aka seed value (for example, 0) and 

the incremental value (for example 1). Once you have defined this property and you insert a record into the table for the first time, it will add 0 for the Identity column.  upon the 2^ndtime you insert a record it, will have a value 1 (seed of 0 plus the incremental of, 1),  and at 3^rd record insertion it would have a value  of 2  (1+1). Download the demo script here Part2_1 which illustrates the table definition with Identity column)

Advantage of using an Identity column as a Primary Key:There are couple of advantages using an Identity column as a Primary Key, including:

• SQL Server itself manages the  Identity values
• Developers don’t need to write a code for new primary key value because Identity column value is created automatically
• The size of index will be low as compared to index size on char based columns

Are there any catches?
While there are couple of advantages, there are some disadvantages which makes managing Identity columns a bit harder including:
1. You cannot control Identity values, because this column and its values are controlled and managed by SQL Server. Please note, however, we may use ‘set Identity_insert tblName on‘ to insert an Identity value explicitly.

2. For any reason, if insertion fails, a value for Identity will get created. Then the next time you insert a record it will create gap in numbering. Let us check this out with an example. Create a table called TestIdentity with three columns: one Identity column, one varchar(10) and  one varchar(5) column. We’ll insert 10 records here, and then 11^th record will fail because we’ll try to insert a value which is greater in size then what is defined in table definition. Once it has failed, we’ll again pass the correct values. Please notice the gap created in the Identity value. Download the demo script here Part2_2

3. You cannot add an Identity property to an existing column. There are only two options that you have if you need to add an Identity column to a table – drop and recreate the table or add a new column with Identity value (and then drop the existing column, if applicable.

4. There has been a lot of said about the disadvantage of Identity columns , especially when it comes to a replicated database., you have to alter your identity property and say “Not for Replication”.

Then, if you have to restore your replicated database for any reason you will have to be very careful, as you will have lost all your replication settings unless you specify that you wish to  keep_replication. For further information on what all we have to aware of when we back up or restore replicated database please refer to this article. Whenever the Identity has reached its threshold value you will have to reseed the Identity values and adjust Identity range at publisher end. This happens when we restore backup at subscriber end; you will have to find the last inserted Identity value and adjust the Identity range at publisher, using these steps:

a. Execute select ident_current(‘tblName’) for every subscriber you have

b. Note down the highest value

c. Go to your publication server and execute dbcc checkident(‘tblName’,reseed,value*+1) where value = value found in step bd. Finally execute sp_adjuste. Now, go to your publication server and execute sp_adjustpublisherIdentityrange PublicationName, TblName

Note: above case is for Merge Replication. Here is a nice article on MSDN on Replicating Identity Columns, and you may also refer Hilary Cotter’s article Identity Crisis (generally known as Identity Crisis). I personally believe that one should avoid using  the Identity property , especially as a primary key when you have replicated database to over come  the issues you face with Identity columns. Rob Volk has written an interesting observation on Identity and Primary Key. Yes, doing this will require some more efforts on your part to create incremental values for your Primary key; but it really is just a matter of writing a few more line of code. Additionally, there are alternate ways of doing this – for more information you may refer an article here on how to generate auto incremental values in SQL Server.

The post Common Mistakes in SQL Server – Part 2 appeared first on SQL Server DBA Services - INDIA, SQL Server DBA, Consultation, SQL Server Expert, Remote DBA, Hemantgiri S Goswami.

There are some points I think we have to be careful when we design a database or an application go live, this will save our lots of effort in future!!

So, what are the Common Mistakes done in SQL Server?

Well, as I said they are so common that sometimes we just overlooked them and we came to know the significance of these pointers when we got trapped. Based on my experience I’ve pen down 10 points below; this is the part 1 of the series so keep visit this place.
I hope this would have answer some common questions like:

• What data type to be used?
• Difference between varchar and nvarchar!! and varchar vs nvarchar
• Likewise you can compare different data types

The first thing which I’ve noticed is length of the data type to be used. One have to be very careful here, one should not use fixed length of data type when one is not sure about the length of the data which is being inserted either from website or from an application; if one fails to choose the correct length it will unnecessarily occupy the valuable disk resource. For example when data which is to be inserted is “Address” and we are not sure of the length we should use variable length data type such as varchar and when we are sure of the length of the data type we should use fixed length data type such as char for “Gender”.

There are cases when I’ve seen where Unicode data are not stored but people have used nvarchar data type for their websites and applications . This will give you a pain when database size grows over a period of time and one is trying to figure out what are the areas we’ve to consider!! nvarchar or nchar should be used when application or website requires to store data in Multilingual format. Both, nvarchar and nchar occupies double the space of stored data the reason is, ncahr or nvarchar uses UTF-16 (16 bit per character ) where as char or varchar uses UTF-8 (8 bit per character) .

Let’s see an example, create a table with 2 column (FirstName and LastName) which uses varchar and nvarchar data type respectively to store data. Now, insert record and then use function len() to count number of characters inserted and datalength() to know how much space it has occupied. Download the script

Also, we should be very careful about the trimming the white space this should be taken care either at front end or at back end.
Here, what I am advising is to use nvarchar or nchar whenever you required to add multilingual support and try to limit it to where it is required the most, say a particular column.

Data Types: It requires careful attention when we design a table and choose type of data we are going to store here and what Data Type we are going to have for particular column of a table! And, that is why choosing right data type is most crucial part of database design, because, if one failed to choose the right data type there could be several pitfall he/she would encounter as the database size grows.

As I said earlier this is the part 1 of the series so keep visiting this place!!

Please do let me know your feedback in comment section.

The post Common Mistakes in SQL Server – Part 1 appeared first on SQL Server DBA Services - INDIA, SQL Server DBA, Consultation, SQL Server Expert, Remote DBA, Hemantgiri S Goswami.

As you are aware, PCI is global payment security standard council. Following their standards help an organization achieve a compliance certificate that all the card data that is processed, store and transmit are maintained in secure environment.

Here is the checklist as I have promised in my previous article. Please do let me know if you find something is missing and you want me to add it to the check list.

The post SQL Server Security Checklist appeared first on SQL Server DBA Services - INDIA, SQL Server DBA, Consultation, SQL Server Expert, Remote DBA, Hemantgiri S Goswami.

At times when applications are run in geographically restricted areas, there is less chance of their exploitation. However, with the spread of the internet and the availability of applications over the internet, the security of data, databases, and database servers has become vital. Because the Data is new GOLD.  In this very article, I will share a few pointers you can consider while hardening your SQL Server according to Microsoft SQL Server Security Best Practice.

Please do not forgot to visit this place next week as I am going to share a checklist that you can use while you are working on hardening your SQL Server.

Why SQL Server Security has been so important!!

Well, every organization, institute, company, or government has their data that is vital to them, and not to be accessed by the authorized person or entity only. That is vital to set some security standards for such critical data so that you can protect your data as much as you can with proper security mechanisms and a set of standards within the organization and in your scope for MS SQL Server Security.
I have penned down some pointers/criteria that require serious consideration when it comes to securing MS SQL Server.

1. Guest User Account: It is always good practice to disable a guest user account; this will keep our server robust from a skilled user who can make use of a guest user account to gain access to the server.

Skilled users always look into a guest user account to establish a NULL session with which he/she can compromise the server.

2. Public Account: Do not grant permission to the Public role, as every single user is a member of this role; hence if you grant any permission to this role it will be available to every user and that will breach security standards.
3. System Administrator Account: We would need to rename and disable the System Administrator account as best security practice; to do so we need to create an integrated account that has SA permissions then create users and assign them appropriate permission based on the fact who needs to access what data!! One of the best practices is also to rename or disable the SA account, it is advisable that we follow this best practice. However, make sure you have one account having equivalent permissions as of SA, also advisable to remove the “Built-in\Administrator” group.

4. Application Roles: While answering threads at Microsoft/Non-Microsoft Forums for SQL Server most of the time what I’ve found is developers/users/dba(s) just avoid creating and/or maintaining schemas/application roles. Actually, it does lots of help to us in terms of rework (granting and/or revoking permissions) and easy manageability of SQL users. Above all if a guest user is not mapped to the application role of the database, the guest user cannot access to database object; we can audit the application role’s activity.

5. Strong Password Mechanism: This is the most vulnerable thing; if our password mechanism is not that strong, one can easily get into our box and steal our data or can do whatever he/she wants to do with it; Sometimes we may find users with NULL/TRIVIAL passwords. Again, this is much more critical if the application is accessed over the Internet we are openly inviting thieves!!!

6. Access port: By default SQL Server listens to TCP port 1433 and UDP port listens to 1434 which is known to everybody; we would need to change it to another port then default and a firewall rule should be created accordingly for exceptions. 

7. NTFS file system: The NTFS file system was introduced in early 1993 with the launch of Windows NT 3.5, This is a file system that has some good features that were not available in the FAT 16 and FAT 32 file systems. File and Folder level security is the key benefit of this file system which also benefited us to keep our SQL Server files secure by assigning appropriate permissions.

8. Updating Server: Microsoft keeps releasing service packs (SP) and hot fix time-to-time to keep software more secure, robust, and bug-free. It is recommended that we should update our box with the latest SP(s) and hot-fix on a regular basis.

9. Audit: We should enable audit for login failures and warning errors which need to be monitored on a daily basis so that if any error, login failure or suspected login attempt is notified we can take necessary action based on the facts available in Log, this way we can foresee any probable vulnerability or can avoid it to be happened.

10. Integrated Logins: Using this feature one can assure him/herself that the SQL box is more secure; integrated/Windows authentication* uses a domain account to access server, database, and database objects. Here, whenever a user tries to access the SQL box his/her account is validated by the domain controller first and then permitted or denied to access the system without requiring a separate login id and password; after this, it will check with SQL Server for the kind of permission this user(s) has.

The other benefit is one can use encrypted passwords, and various handshake methods like PKI, Kerberos, EAP, SSL Certificates, NAP, LDP, and IPSec policy; this will ensure our highly critical data are being sent securely over the network.

11. Instances: We can create different Instances to isolate the development/production environments from each other; and/or isolate users from accessing databases that are not meant for them. This can be done by application roles/fixed DB / fixed server roles but using instances we can hide the names of the databases from the users this way they don’t even come to know which databases are there on the server and who is accessing which database.

12. Service account: Always use the least privileged user account to start the server and agent service on the server. A domain account with the local admin privilege is enough to start the services; a domain user account for services is required if we have to work on some special services that require network access also like replication, log shipping, mirroring, cluster, remote procedure calls, backing up-restoration on or from network and remote data access.

13. Network Library: Don’t install and allow network libraries except those that are required.

14. Isolated from IIS: Though we can have both SQL Server and IIS on a single machine; it is advisable to keep them on a separate machine. The idea is; that even if the IIS server is compromised our database(s) are secure, the other benefit is if more memory and processor resource is eaten by the IIS server SQL box will not suffer.
15. Stored Procedure: We should have to make a practice to wrap DML statements in SP(s) to avoid SQL injection.

16. Monitoring SQL Server and Windows Event Viewer: We shall keep our eye on SQL Error Log and Windows Event Viewer for any suspicious activity on a daily basis so that we can take corrective action(s) as soon as it is identified.

17. Encrypting data: I recommend you leverage Transparent Data Encryption aka TDE for all the user databases you have on your system.
18. Linked server: Prevent access to the linked server from those users who don’t need access by assigning proper privileges.

19. System Stored Procedure(s): System stored procedures (SP) like xp_cmdshell, xp_regread, xp_regwrite needs to be restricted to access. Using this SP(s) one can easily read, modify, or delete registry information or can manipulate system information.

20. Anti Virus: We should install an Antivirus on SQL Box and exclude SQL server database files; this way we can be assured of Virus/Trojan/Malware/Spam attacks on our servers that could harm our database files.

Conclusion:

As time passes Securing data has become the most vital part, and we must agree and honor it. This is the information that contains our financial, social, business, and historical data; and as a DBA it is our prime responsibility to make sure that this has been taken care of and is secure enough. These are the key points that I’ve collected so far, If anybody would like to draw my attention to some point that I might have missed out, can write back their comments here.

Photo Credit : Free Stock photos by Vecteezy

The post How to Secure SQL Server appeared first on SQL Server DBA Services - INDIA, SQL Server DBA, Consultation, SQL Server Expert, Remote DBA, Hemantgiri S Goswami.

What is On-Premise?

As we know, by definition On-Premise is a term that we use when our data, application, server(s), or data are kept at one of the premises of the Organization.

What is the Cloud?

Cloud – This is one of the best things that happened in this era. Basically, the cloud is a service offering using which we can host our server(s), application(s), server(s), or data in a virtual environment.

If you are a business owner, IT administrator, or staff working with the technology you will see this question coming to you. At some point, you’ll have to decide if your organization will go use the cloud technology, or it should not.

In this white paper, I have tried to compare both the environments – the cloud and the on-premise. I have tried to explain things using the example(s) and comparing 5 very important and deciding aspects.

I am certain that this white paper will answer your question “The Cloud or On-Premise – is it a go or no go?”. But, if it is not, please do let me know the same via comment or email.

You can download the white paper from here. https://1drv.ms/u/s!AsCKJTQWicaTgdseAlmfEL6BA7xhJg

The post The Cloud or On-Premise – Is it a go or no go? appeared first on SQL Server DBA Services - INDIA, SQL Server DBA, Consultation, SQL Server Expert, Remote DBA, Hemantgiri S Goswami.