SQL Tips by Namwar Rizvi

April 5, 2010

Online Content Vacuum Technology in SQL Server 2008

Filed under: Information,SQL Server 2008 — namwar @ 9:06 PM
Tags: ,

As per SSWUG.org reports in their newsletter, Microsoft has announced an excellent and cool feature for Data Mining lovers. It is Online Content Vacuum Technology. Following is the announcement from Microsoft:


SEATTLE, WA – Microsoft today announced that the next release of SQL Server would include a new data scraping and categorizing utility, code-named “Vacuum” in the next CTP release of SQL Server.  The functionality allows SQL Server to apply its world-class search technologies to content found on the web, and allows companies to “bring the cloud” down to their local networks for searching.

“This technology will be key to using many of the vast resources that are simply too tough to navigate, too tough to find what you need in today’s world,” says Steve Ballmer, Microsoft CEO.  “The Internet and social networks, blogs and other resource represent the biggest form of business intelligence resources available, but incorporating them into your work is difficult.  By using Vacuum, you simply identify the root sources of information you trust and let SQL Server pull in and categorize information for you automatically.  From there, use the new features of SQL Server 2008 R4.1 to query, report on and learn from all of those sources.  It’s what business intelligence needs to be to really enhance your ROI.”

By using Vacuum, users will be able to provide instant backup to the data represented in their systems.  At the same time, by pulling data from the cloud and bringing it onto the local network databases, companies can better control information presented, can update their databases and can avoid the abstract nature of working on the Internet.

“We’re excited to bring this announcement to the IT Professional community.  It’s clear that we can provide real value by automatically tying online resources to reports and other local resources for reporting.  We’ve been working very hard on the fact-recognition logic in SQL Server 2008 R4.1 so that it can relate information on your systems with information online, bringing the two together automatically,” said Donald Farmer of Microsoft.  “We spent a lot of time building out the great functionality in the PowerPivot toolset.  By adding this Vacuum tool to the mix, we’ll be able to back up any fact you may be looking to share, all automatically.  It’s simply incredible.”

The release of the Vacuum tools should be ready in the next CTP of SQL Server 2008 R4.1.

March 29, 2010

Search anything in your Database schema right from SSMS

In my one of earlier articles here I wrote about a way to find any value in any database column. Today, I found a great free tool, from RedGate, to find dependencies of any object. The tool “SQL Search” is available here. This can be used for impact analysis, improving the performance by replacing “Select *” with actual column name etc.

The best part is that it installs as an SSMS plugin, making it very easy to use it.

I recommend every SSMS user to give it a try.

April 13, 2009

SQL Server Client Tools Setup Fails due to MSXML 6

If you are trying to install SQL Server 2005 client tools and continuously getting failure message because of MSXML 6 installation which already exists on your machine then it may be due to the corrupted installation of MSXML 6 which blocks the installation of client tools. You can fix it as follows:

Go to Control Panel-> Administrative Tools->Add Remove Programs and try to remove it. If it removes smoothly then just install the Client Tools as before and you are done. 

OR

If you found that you are unable to uninstall the MSXML 6 because of some wiered error then you need a tool called Windows Install Clean up from here

Donwload it and remove the corrupt installation of MSXML 6. Once removed, you can now install the Client tools as usual.

January 15, 2009

How to use more than 4GB RAM on SQL Server?

SQL Server is an highly optimized and well tuned piece of code. It try to utilise Operating System resources as optimum as possible. Due to this, most DBA think that if they will increase RAM on the server then SQL Server will automatically start using it, right? Wrong, SQL Server can only use RAM more than 4GB if and only if,

  • You are running Enterprise Editions of Windows 2003 Servers orAdvanced/Datacenter version of Windows 2000
  • You have enabled Operating System to use the extra RAM you have installed. Please check here for the details and how to do it.
  • You are not running Express or Workgroup version of SQL Server. Please check here for further details about limitations in different versions of SQL Server
  • you have enabled awe enabled option and set max server memory to the maximum memory you can allocate to SQL Server. This is applicatiable for 32 but versions of OS and not required in 64 bit version of Windows servers. Please check here for further details and how to configure the memoryfor SQL Server

January 1, 2009

Review of SQL Server 2005 Service Pack 3

Filed under: Information,SQL Server 2005 — namwar @ 8:58 PM
Tags: , ,

Microsoft has released the third service pack for SQL Server 2005. You can download it from here.

As many of us are still using SQL Server 2005 and has not moved to SQL Server 2008 yet therefore, I felt necessary to give you a breif overview of what has been offered in third service pack for SQL Server 200. Following is an outline:

1. SP3 contains all hotfixes for SP2 along with some new features.
2. If you are not ready to implement the SP3 yet then you can apply Cummulative update 11 for SQL Sever 2005 Service Pack 2 available here
3. DBCC commands will display all error messages in SP3 in contrast to SP2 where you are only able to see first 200 messages if you do not specify ALL_ERRORMSGS option. In SP3 ALL_ERRORMSGS option does not have any meaning.
4. Notification services (which has been discontinued in SQL Server 2008) of SQL Server 2005 will work with both SQL Server 2005 and SQL Server 2008 database engine after applying SP3
5. There is a small update in Replication area i.e. a stored procedure sp_showpendingchanges now has one additional parameter @show_rows. This is a flag which allows you to specify two additional columns in the resultset for more detailed information. Further information is available here
6. There are three updates in Reporting Services area:

* Report models can be created for Terra data databases. Further information is available here
* PDF rendering extension can now embed the font to display the document correctly on a computer where document fonts are not already installed. Further information is available here
* Sharepoint integrated mode has been optimized by reducing the number of calls to Sharepoint object model. Reports will render faster now.

I hope this overview will give you a brief idea about what is new in SP3.

Happy New Year to all readers and best wishes for 2009.

August 28, 2008

Resource Governor in SQL Server 2008 – Beware of catches

Resource governor is one of the best additions in SQL Server 2008. It gives you a real control on your environment as a DBA and can be very useful for better utilization of your database server resources. So far so good, but don’t just start using it without knowing how it actually restricts resource utilization.  Following are some points to remember for better utilization of this feature:

  1. Normally, most of us assume that if we restricts a memory usage for a user A upto 10% only then he will never be able to utilize more than 10% of server in any case. Right? Wrong. Actually, Resource governer only restricts user to utilize not more than 10% if it feels that remaining memory is not available but if memory is available and there are no pending workload then it will allow the user to use more than its allowed quota of 10%. This is there to optimize the utilization of memory and avoids wastage of resources. But it can have worse effects also because if User A fires it query before other users then server will start utilizing all the available memory and all other users which came afterwards will suffer the consequences.
  2. Please note that Resource Governor is for database engine not for other services in SQL Server 2008. It means you can not control usage of Reporting Services, Analysis Services or Integration Services.
  3. If you have multiple instances running on same machine then you can not use Resource Governor to manage load between these instances.

Keeping these points in your mind will help you to better understand how to use resource governor and what to expect. It is one of the best tools to manage your load and highly recommended but make sure you know the pros and cons of it.

August 7, 2008

Welcome to SQL Server 2008

Filed under: Information,SQL Server 2008 — namwar @ 10:20 PM
Tags: ,

Finally, wait is over and just one day before start of 2008 Olympic, Microsoft has released SQL Server 2008. It is a big day as many of us were waiting for the final release to start taking architectural decision based on new technologies offered by SQL Server 2008. The 180 day trial version is available here
To start with, I will recommend to watch following videos covering disfferent aspect of technologies used in SQL Server 2008
SQL Server 2008 Demos and Videos
You can also check these Web Casts

In my next articles, I will try to cover some fundamental technological advancements in this version of SQL Server 2008.

July 31, 2008

SQL Server Panel Interview

Filed under: Information,SQL Server 2008 — namwar @ 10:14 PM
Tags: ,

I participated in a panel interview of SQL Server DBAs conducted by OdinJobs This interview an email interview and following other industry experts also interviewed (listed in ascending order)

This interview covers what issues faced by SQL Server DBAs, new features of SQL Server 2008 etc. It is available at

I will recommend everyone to read this as it gives you a good idea about genearl thoughts of DBAs

July 18, 2008

How SQL Server Index Statistics works – Part 2

Filed under: Information,SQL Server 2005,TSQL,Tuning — namwar @ 11:36 PM
Tags: ,

In my last article,

How SQL Server Index Statistics works – Part 1

I described the concept behind SQL Server index statistics. Today, I will present the real world example from AdventurWorks database. Launch Management Studio and execute the following script

Use AdventureWorks

Go

DBCC SHOW_STATISTICS (‘Sales.SalesOrderDetail’,‘IX_SalesOrderDetail_ProductID’ )

Go

You will get the result similar to this:

The fist resultset tells you about the basic details of statistics collected for the index IX_SalesOrderDetail_ProductI You can check when was the statistics last updated. If it is too old then you need to update the statistics.

Second resultset guides you about the density of frequent values. Frequent value means the value which appeared more than once in the step. The All density considers frequent values and Density considers non-frequent values only.

Third resultset is actually the most intersting section. This is the histogram of values of the indexed column of the index or the first column of the composit index. Description of its columns is as follows:

 

Column

Details

RANGE_HI_KEY

Upper bound value of a histogram step i.e. the highest value in the step. The first step is the lowest value for the column in the table.

RANGE_ROWS

Number of rows from the sample that fall within a histogram step, excluding the upper bound. By definition, this must be zero in the first sample.

EQ_ROWS

Number of rows from the sample that are equal in value to the upper bound of the histogram step.

DISTINCT_RANGE_ROWS

Number of distinct values within a histogram step, excluding the upper bound.

AVG_RANGE_ROWS

average number of duplicate values within a histogram step, excluding the upper bound. This is defined as:

(RANGE_ROWS / DISTINCT_RANGE_ROWS

for DISTINCT_RANGE_ROWS > 0)

By using this histogram, SQL Server determines the expected no. of rows to be returned for a given query. For example: in the above if you see the highlighted section, you will noticed that in the 80th step we have

RANGE_HI_KEY=819

RANGE_ROWS=218

EQ_ROWS= 195

Firstly, it means that for range of ProductID>815 and ProductID<819 we will get 218 rows. This can be verified by executing the following query:

Select count(*) from Sales.SalesOrderDetail where ProductID>815 and ProductID<819

Result is 218, which confirms the RANGE_ROWS statistics value

Secondly, it also means that for ProductID=819 we will get 195 rows. This can be verified by executing the following query:

Select count(*) from Sales.SalesOrderDetail where ProductID=819

Result is 195, which confirms the EQ_ROWS statistics value.

I hope by seeing these examples, you have got the good idea how SQL Server forecasts the total number of rows to be returned and how they are used in analyzing the cost of index usage.

September 7, 2007

Data Services: Get ready for Microsoft’s new project called Astoria

Filed under: Information — namwar @ 8:32 PM

Today’s programming of web has been changed dramatically with the advancement in technologies like AJAX, RSS, ATOM and Mehsups applications. Some of these are protocols, some are data formats and some are just the aggregators to get data from somewhere and add value on top of it in one way or other.
Microsoft is working on a project code name called “Astoria“. This project aims to provide pure Data Services. Pure Data Services means you can make your data accessible on the web without specifically creating different web services for different type of requests.
It is actually a concept of exposing data totally independent of format in a uniform protocol.

Suppose you have created the data service for your database NorthWind then a user can get the data directly by accessing URI and can navigate to the data hierarchy by adding predicates in web request.

For example (From Microsoft document here):
You want to retrieve the list of customers it will become
http://myserver/data.svc/Customers

Now you want a specific customer of id ALFKI, you will write
http://myserver/data.svc/Customers%5BALFKI%5D

Server will return data in XML,JSON or any other supported format based on user’s request

In this way you can build widgets, meshups,tickers, Flash graphs etc. where UI will natively communicate with data.

This project is in very early stages and you can get more information from here
Astoria FAQ


Next Page »

Blog at WordPress.com.