Recently the ETL load times of my Datawarehouse are increasing and so I decided to check the Indexes status on the tables . I found some fragmentation on the indexes and also some Statistics that are not being updated regularly . I searched online and found this blog by Ben Nevarez(SQL Blog).
I decided to check the fragmentation levels of the Index after the ETL load is done daily on a regular basis. So I digged deep into the DMV’s and got this script below
select
OBJECT_NAME(ps.object_id) as TableName
,si.Name
,ps.Avg_Fragmentation_in_percent
,STATS_DATE(ss.object_id,ss.stats_id) as LastUpdatedStatistics
from
sys.dm_db_index_physical_stats(DB_ID(DB_NAME()),null,null,null,null) ps
join
sysindexes si
on
ps.object_id = si.id
and
ps.index_id = si.indid
left outer join
sys.stats ss
on
ss.object_id = ps.object_id
and
ss.name = si.name
where
ps.avg_fragmentation_in_percent > 10
order by
ps.avg_fragmentation_in_percent desc
I published the above query in my Reporting services suite of Operational Reports and check it daily morning. The report shows all the Indexes with more than 10% percent fragmentation along with Last updated Statistics time . If you want to go further.You may want to rebuild all the indexes that are above 20% fragmented in TSQL. If there is a better way of doing it please comment.
Recently I have read a lot of Blog posts on how to export files in different formats. But I use a custom procedure which exports text files in Pipe Delimited format using BCP.This procedure can be used in other processes for Output and Error Handling.
Here is the Script for the procedure.You can modify the below procedure using any of the delimiters.
Many Clients have varied Requirements for Dates. You can do the conversions in Date format using T-SQL from the following link in MSDN.
In reporting services you can convert datetime using the customs visual basic datetime formats using Expressions.
Here are the following links on MSDN for Visual Basic Datetime Formats Predefined DateTime Formats Using Format Function
If the sql server reporting services is configured on the server called “SQLBOX” then the URL is http://SQLbox/reports.
If there is a named instance like SQLBOX\VIVEK then the reporting server URL is http://SQLBOX/reports_vivek
Today I stuck with an interesting problem of converting number data in Varchar fields to numeric datatypes. I have created the following sample data
Create table SQL script
The data in the table contains Decimal and scientific values in varchar format as shown in the figure below.
Now when I tried to convert the above values to numeric as below it returns only 3 rows but with the following message
Wrong SQL
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
The isnumeric does return 1 for all the 4 values in the table. I modified the query so that it initially converts to
float and then converts to numeric and it works.
Storing the list of email subscribers is quite often seen in the web based companies. Once the emails are stored they are segmented based on the customer interests and products they have purchased on the website.
In this article I will show the simple step of extracting the Account Name and Domain Name using T-sql.
Expressions are powerful in Integration Services. With Expressions you can dynamically set the servername (based on the environment) or the name of the excel file(if it is a fixed format). Let me give you an example.
I have a simple Dataflow task(as shown below) that gets the data from a table and exports it in a Tab delimited text file.
The Data Flow Task
I have set up a following variable called ServerName where I can change the value dynamically on the Connection String.
Variable Server Name
To configure the ConnectionString to use the Variable name dynamically at RunTime we need to configure Dimensions. This is how its done.Right Click on the Connection String and click properties(or click F4 by highlighting Databaes connection)
Database Connection Properties
In the Properties section click on the Expression for configuring nd also setting the Servername settings as below.
Expressions_Connection_Properties
Select ServerName
Configure Value as ServerName
Click Ok twice and the expressions are now configured. The best way to check it is to use the Bidshelper. (www.codeplex.com/BidsHelper) In the below diagram as you notice the Pink colour on the edge of the Connection String Icon which represents that the connection is configured via Expression.
The above example should give you a good example on how to configure Connection Strings using Expressions. Just keep commenting .
The error happens when your disk space is full on TempDB.Check the space on the Drive where the TempDb is located .
Solutions
a.Review the Query and check if it can be better optimised. Some of the Queries’ intermediate heavy datasets are stored in tempdb before returning the results.
b.The most easiest way I found is to restart the SQL Server service. Every Time the SQL server is restarted the tempdb gets flushed thereby reducing all the disk space.
