How to insert comma - delimited string value in a table

How to insert comma - delimited string value in a table

Here is the script that will insert comma separated string (values separated by comma). String may have only one value as well. This script will work in both the cases.

declare @string varchar(50)

declare @tabvar table(val varchar(50))

set @string = 'a,db,c,dd,eee,f'

while(LEN(@string) > 0)

begin

       if(charindex(',', @string) > 0)

       begin 

              insert into @tabvar

              select substring(@string,1 , charindex(',', @string) - 1)

       end

       else

       begin 

              insert into @tabvar

              select @string

              break

       end

       set @string = substring(@string,charindex(',', @string) + 1, len(@string))

end

select * from @tabvar

Why Transaction Log is Growing!!!

Why Transaction Log is Growing!!!

Reasons for Trnsaction Log Growth

Following are the basic reasons for Transaction log growth:

1. When we do bulk insert/update operation.
2. Your database is in Full Recovery model and you are doing Index rebuild operation. Or your database is involved in Mirroring/Log shipping and you are doing Index rebuild operation.
3. If you have Replication implemented in your environment and something is failing in that, you may see transaction log growing unexpectedly.
4. Check the Auto growth value. I have see most of the times high percentage of this causes transaction log growth.

How to Troubleshoot

1. Backup the transaction log if you have enough disk space and your database is not in SIMPLE recovery model.
2. We can user DBCC SQLPERF (LOGSPACE) to find out the current used percentage of the transaction log for the specified database.
3. There could be some transaction running on the server that could be recovered later. Kill those transactions. We can use the command DBCC OPENTRAN.
4. We can try shrinking log file by using command DBCC SHRINKFILE. We may need to user BACKUP LOG with TRUNCATE_ONLY. This is what I use in my QA environment if I face any issues with transaction log growth.

USE <Your DB Name>

GO

CHECKPOINT

GO

BACKUP LOG <Your DB Name> TO <Backup Device Name> WITH NOFORMAT, INIT,  NAME = N'<Your DB Name>-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

DBCC SHRINKFILE(<Name of the log file>, 1)

GO

Tips To Write A Good Stored Procedure

Some Tips to write a good stored procedure

Coding SPs is a very common task in the database world. This is done not only by database developers, but also by application developers. Most of the smart DBA also keep himself/herself involved in writing and reviewing stored procedures. One of the interviews I was asked “How good you are in writing complex stored procedures” and “What are the major tips you keep in your mind when you write the stored procedures as DBA”.  Notice the last word added by interviewer “as DBA”. You should consider all the performance issues when you answer this question. 

Stored procedures are having more importance in the database as it reduces network traffic and improve performance. In this post I have just tried to add some tips to write good stored procedures. I hope this will be helpful for fresher and for the experienced developers (Application/database).

1. Always maintain better readability in the stored procedure. For this use proper indentation and make sure you specify SQL keywords in upper case. For Example instead of ‘Select’ write ‘SELECT’. This makes other developers to understand the code properly. If you do this at first site you are the good programmer/developer.  

2. Until you need so many variables in the SP, use as few as possible variables. Remember doing this will free some spaces in cache and performance would be better.

3. We should avoid dynamic queries in the stored procedures. In case if dynamic queries each time SQL server recompiles the statement. It has very bad performance impact.

4. When you are calling stored procedures, always use fully qualified names. The format for fully qualified name could be “DB_NAME.SCHEMA_NAME.TABLE_NAME”.  Whenever you create the SP always use schema name in the create procedure statement even though the schema is dbo.

5. As I mentioned before SPs reduces network traffic and increase the performance. I have seen so many SPs written by developers where SET NOCOUT ON has not been used. If you don’t use this SQL server returns the message that shows number of rows affected. This can cause extra network traffic and can have major impact on the performace.

6. DO NOT USE ‘sp_’ prefix in a stored procedure name. This is reserved for system stored procedures in the master database. If you use the prefix ‘sp_’ then basically you are making SQL server to have an extra look up in the master database.

7. Most of the time we call other SP(s) in one SP. We should always use sp_executeSQL instead of directly EXEC.

8. When we assign value to a variable, we use SET. We can also use SELECT statement. The difference between both is: By using SET you can assign value to one variable at a time but by using SELECT you can assign values to multiple different variables. So, instead of multiple SET statement use SELECT statement. This is much faster than multiple SET statements.

9. Avoid unnecessary conditions in the WHERE Clause. Avoid using IN instead use EXISTS. This gives better performance as IN considers the NULL value also. Remember result set for IN is heavier than EXISTS.

10. Avoid using DISTINCT and ORDER BY until you need this. It makes the performance slower.

11. We should not use CURSORS instead use temp table/table variable. If you need iteration then create the table with identity column and use WHILE loop.

12. Avoid using “SELECT *”. Instead specify column names (required) in the SELECT clause.

13. What we should use SUBQUERY or JOIN? Remember SUBQUERY works faster in large tables whereas JOINS works faster in small tables. You should use both keeping this in mind. Also avoid using correlated sub queries and this affect the performance of the query.

14. Try to use table variables instead of Temporary Tables - Temp tables can cause stored procedures to recompile. But table variables were designed specifically to guard against stored procedure recompiles during execution. If the result set is not containing a huge number of records then you should stick to table variable, otherwise temp table has its advantages. There is a misconception that temp tables always use the tembdb database but table variable do not. Table variables also use tempdb after a certain size. 

15. Use proper indexes - You can use the help of the data tuning advisor, but it does not gives the proper result all the time. Index scans are much faster than table scans. So identify the table scans from the execution plans. But when a table returns smaller rows, then it is better to use a table scan. 

Remember for the good performance rules could be broken if needed. We should keep in mind that for any firm database performance is the ultimate goal.

Hope these tips help you to some extent. There could be some other tips that I can add later.

Find Blocking and troubleshoot

What is Blocking:

One process is already acquired the locks on a set of rows and other process tries to acquire a lock on the same set of rows. Due to this other process has to keep waiting until first process releases the lock by finishing its job.

Find blocking:

We can use sp_lock, sp_who and sp_who2 system stored procedures to find the blocking session. After getting the SPID, we can user DBCC INPUTBUFFER (SPID) to find the SQL statement that is causing blocking. The returned SQL statement will not be complete SQL statement instead ii will have first 128 characters only.

To see the complete SQL text we can use dynamic management view sys.dm_exec_session and dynamic management function sys.dm_exec_sql_text(sql_handle).

To resolve blocking, you can disconnect the connection causing the blocking using KILL command. If this does not solve the problem permanently, then rewrite the stored procedure causing the block more efficiently.

What is Lazy Writer Process

The job of the lazy writer is to find dirty pages in the buffer pool and write them out to disk and drop those pages from cache.

It does this to keep a certain amount of free pages available within the buffer pool for data that may be requested by other queries. The pages that it writes out are ‘older’ pages, ones that haven’t been used for a while.

If there’s lots of available buffers, the lazy writer will be doing relatively little work and the number of pages written to disk will be quite low. If the lazy writer’s consistently writing lots of data, it may indicate that there’s a memory bottleneck.