Formatting numbers for decimals and significant digits in JavaScript

Formatting numbers so they confirm to a specific format can be deceivingly tricky. For example, one of the most common tasks is to format a number for currency display- an integer followed by two decimals. You may be tempted to use number rounding to first shift the number's decimal places (via multiplication), round it, then shift the decimal back (via division) to pound the number into your hard earned dollar, though that won't work in many cases. For example, consider the number 120. Number rounding certainly won't get you to 120.00.

To easily format numbers for a specific number of trailing decimals or total digits (aka padding), JavaScript 1.5 introduces the below two nifty methods:

Number.toFixed(x) :Formats any number for "x" number of trailing decimals. The number is rounded up, and "0"s are used after the decimal point if needed to create the desired decimal length.

Number.toPrecision(x):Formats any number so it is of "x" length. Also called significant digits. A decimal point and "0"s are used if needed to create the desired length.


The best way to see all the subtleties of toFixed() is to see it in action:

var profits=2489.8237
profits.toFixed(3) //returns 2489.824 (round up)
profits.toFixed(2) //returns 2489.82
profits.toFixed(7) //returns 2489.8237000 (padding)

Displaying any number in currency format can't get any easier!
Number.toPrecision()

To toPrecision() now:

var anumber=123.45
anumber.toPrecision(6) //returns 123.450 (padding)
anumber.toPrecision(4) //returns 123.5 (round up)
anumber.toPrecision(2) //returns 1.2e+2 (you figure it out!)

toPrecision() is useful if your number must be of a certain length.

Browser considerations

Now, as noted, our two heros above are JavaScript 1.5 methods. What this means is that they'll only work in IE5.5+ and NS6+. The issue of legacy browsers not performing the desired formatting operation not withstanding, how do you ensure that these two methods at least degrade well? Well, by using method detection in your code. For example:

var profits=2489.8237
if (profits.toFixed) //if browser supports toFixed() method
profits.toFixed(2)

For those of you who also need to ensure legacy browsers such as IE5 also perform the desired number formatting operation, well, then it's time to roll your own function. But be warned, it won't as pretty as what has taken place here!

Examples of how to Calculate Different SQL Server Dates

Every now and then, you need to take the current date and calculate some other date. For instance, you might have an application that needs to determine what date is the first day of the month, or need to know the last day of the month. Now most of you probably already know how to separate the date into its piece (year, month, day, etc.) and use those pieces along with a number of functions to calculate a date that you might need. In this article, I will be showing how to use just the DATEADD and DATEDIFF function to calculate a number of different dates you might need to use in your applications.

In order to understand these examples, let's first review the DATEDIFF and DATEADD functions. The DATEDIFF function calculates the amount of time between two dates, where the time part is based on an interval of time, such as hours, days, weeks, months, years, etc. The DATEADD function calculates a date by taking an interval of time, and adding it to a date, where the interval of time will be the same as those used by the DATEDIFF function. To find out more about the DATEDIFF and DATEADD functions, and the different intervals of time read Microsoft Books Online.

Using the DATEADD and DATEDIFF functions to calculated dates requires you to think a little differently about what it takes to convert the current date into a date you need. You must think in terms of date intervals. Such as, how many date intervals it is from the current date to the date you want to calculate. Or how many date intervals is it from today to some other date like '1900-01-01', and so on. Understanding how to look at date intervals will help you more easily understand my different date examples.
First Day of Month

For the first example, let me show you how to get the first day of the month from the current date. Remember now, this example and all the other examples in this article will only be using the DATEADD and DATEDIFF functions to calculate our desired date. Each example will do this by calculating date intervals from the current date, and then adding or subtracting intervals to arrive at the desired calculated date. Here is the code to calculate the first day of the month:

select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

Let me review how this works, by breaking this statement apart. The inner most function call "getdate()", as most of you probably already know, returns the current date and time. Now the next executed function call "DATEDIFF(mm,0,getdate())" calculates the number of months between the current date and the date "1900-01-01 00:00:00.000". Remember date and time variables are stored as the number of milliseconds since "1900-01-01 00:00:00.000"; this is why you can specify the first datetime expression of the DATEDIFF function as "0." Now the last function call, DATEADD, adds the number of months between the current date and '1900-01-01". By adding the number of months between our pre-determined date '1900-01-01' and the current date, I am able to arrive at the first day of the current month. In addition, the time portion of the calculated date will be "00:00:00.000."

The technique shown here for calculating a date interval between the current date and the year "1900-01-01," and then adding the calculated number of interval to "1900-01-01," to calculate a specific date, can be used to calculate many different dates. The next four examples use this same technique to generate different dates based on the current date.
Monday of the Current Week

Here I use the week interval (wk) to calculate what date is Monday of the current week. This example assumes Sunday is the first day of the week.

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

If you don't want Sunday to be the first day of the week, then you will need to use a different method. Here is a method that David O Malley showed me that uses the DATEFIRST setting to set the first day of the week. This example sets Monday as the first day of the week.

set DATEFIRST 1
select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate())

But now if you want to change this example to calculate a different first day of the week like “First Tuesday of the Week” you can change the set command above to “set DATEFIRST 2”.
First Day of the Year

Now I use the year interval (yy) to display the first day of the year.

select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

First Day of the Quarter

If you need to calculate the first day of the current quarter then here is an example of how to do that.

select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

Midnight for the Current Day

Ever need to truncate the time portion for the datetime value returned from the getdate() function, so it reflects the current date at midnight? If so then here is an example that uses the DATEADD and DATEDIFF functions to get the midnight timestamp.

select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

Expanding on the DATEADD and DATEDIFF Calculation

As you can see, by using this simple DATEADD and DATEDIFF calculation you can come up with many different dates that might be valuable.

All of the examples so far only calculated the current number of date intervals between the current date and "1900-01-01," and then added the number of intervals to "1900-01-01" to arrive at the calculated date. Say you modify the number of intervals to be added, or added additional DATEADD functions that used different time intervals, or subtracted intervals instead of adding intervals; by making these minor changes you can come up with many different dates.

Here are four examples that add an additional DATEADD function to calculate the last day dates for both the current and prior intervals.
Last Day of Prior Month

Here is an example that calculates the last day of the prior month. It does this by subtracting 3 milliseconds from the first day of the month example. Now remember the time portion in SQL Server is only accurate to 3 milliseconds. This is why I needed to subtract 3 milliseconds to arrive at my desired date and time.

select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))

The time portion of the calculated date contains a time that reflects the last millisecond of the day ("23:59:59.997") that SQL Server can store.
Last Day of Prior Year

Like the prior example to get the last date of the prior year you need to subtract 3 milliseconds from the first day of year.

select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))

Last Day of Current Month

Now to get the last day of the current month I need to modify slightly the query that returns the last day of the prior month. The modification needs to add one to the number of intervals return by DATEDIFF when comparing the current date with "1900-01-01." By adding 1 month, I am calculating the first day of next month and then subtraction 3 milliseconds, which allows me to arrive at the last day of the current month. Here is the TSQL to calculate the last day of the current month.

select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))

Last Day of Current Year

You should be getting the hang of this by now. Here is the code to calculate the last day of the current year.

select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))

First Monday of the Month

Ok, I am down to my last example. Here I am going to calculate the first Monday of the current month. Here is the code for that calculation.

select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)

In this example, I took the code for "Monday of the Current Week," and modified it slightly. The modification was to change the "getdate()" portion of the code to calculate the 6th day of the current month. Using the 6th day of the month instead of the current date in the formula allows this calculation to return the first Monday of the current month.
Conclusion

I hope that these examples have given you some ideas on how to use the DATEADD and DATEDIFF functions to calculate dates. When using this date interval math method of calculating dates I have found it valuable to have a calendar available to visualize the intervals between two different dates. Remember this is only one way to accomplish these date calculations. Keep in mind there are most likely a number of other methods to perform the same calculations. If you know of another way, great, although if you do not, I hope these examples have given you some ideas of how to use DATEADD and DATEDIFF to calculate dates your applications might need.

http://www.databasejournal.com/features/mssql/article.php/3076421

Understanding INNER join in detail

By Susantha Bathige,

Understanding INNER join in detail.

The objective of this article is dig into detail of how INNER joins produce the result set. The INNER join is the common join type used in many cases. Even though it is used very often I have seen most people are not certain how it produces the result set when changing the join condition.

Before go into the article contents, I need to create the two tables below to illustrate the examples used here.

Table 1

CREATE TABLE #InnerJoinTest1
(
InnerJoinTest1_PK INT IDENTITY(1,1)
)
GO

Table 2

CREATE TABLE #InnerJoinTest2
(
InnerJoinTest2_PK int
)
GO

Since the tables above are created in tempdb, the database currently used in your query editor will not matter. Then I use the queries below to populate the created tables with some sample data.

To keep the explanation easy I used only 5 records in each table. However once you understand the theory behind it you can map it to larger tables as well. The concept is similar despite of the number records in the tables.

Populate table 1

WHILE SCOPE_IDENTITY()<5 OR SCOPE_IDENTITY() IS NULL
INSERT INTO #InnerJoinTest1
DEFAULT VALUES

Populate table 2

INSERT INTO #InnerJoinTest2
SELECT * FROM #InnerJoinTest1

Once you have executed all the queries above successfully, everything is set for our discussion.
The Scenario

I will present you all the queries which involve an INNER JOIN in advance so that you can manipulate and decide how many records each query will return. Finally you can compare your answers with the result set that actual query execution produces by the SQL Server.

Query 1

SELECT a.*,b.* FROM #InnerJoinTest1 a
INNER JOIN #InnerJoinTest2 b
ON a.InnerJoinTest1_PK=b.InnerJoinTest2_PK

Query 2

SELECT a.*,b.* FROM #InnerJoinTest1 a
INNER JOIN #InnerJoinTest2 b
ON a.InnerJoinTest1_PK>b.InnerJoinTest2_PK

Query 3

SELECT a.*,b.* FROM #InnerJoinTest1 a
INNER JOIN #InnerJoinTest2 b
ON a.InnerJoinTest1_PK>=b.InnerJoinTest2_PK

Query 4

SELECT b.*,a.* FROM #InnerJoinTest1 a
INNER JOIN #InnerJoinTest2 b
ON a.InnerJoinTest1_PK<b.InnerJoinTest2_PK
ORDER BY a.InnerJoinTest1_PK

Query 5

SELECT b.*,a.* FROM #InnerJoinTest1 a
INNER JOIN #InnerJoinTest2 b
ON a.InnerJoinTest1_PK<=b.InnerJoinTest2_PK
ORDER BY a.InnerJoinTest1_PK

Query 6

SELECT a.*,b.* FROM #InnerJoinTest1 a
INNER JOIN #InnerJoinTest2 b
ON a.InnerJoinTest1_PK<>b.InnerJoinTest2_PK

Take a piece of paper and manually determine the result set of each query will return.
Cartesian Product

First of all, I would like to mention that I will consider table 1 as set 1 and table 2 as set 2. (according to the set theory).

Let's produce the Cartesian product of the two sets. The Cartesian product of the two sets is given below.

You can produce the Cartesian product when you join every row of one table to every row of another table. You can also get one by joining every row of a table to every row of itself.

It is very important to understand how the result set is produced for the Cartesian product from the queries above. The Cartesian product will give you the maximum possible number of combinations that can be created from the given two sets. The number of records in the Cartesian product can be computed by using the following formula.

Number of records = No.of records of table 1 X No.of record of table2

In our case it is 25. (5x5)

Rationalization

Let's look at the first query. This kind of INNER JOIN is the most popular and often you may be able to determine the result set it produces. This INNER JOIN with the equal condition will give you the matching records of both sets. If you look at the above Cartesian product, how many matching records you will be able to find?

I have highlighted the matching instances in the above Cartesian product. So that the first query will give you the highlighted records as the output.

Then consider the second query.

In this case you need to find all the possible record combinations where the first table value is greater than the second table values.

the image below shows the Cartesian product and I have highlighted the record combination for query 2.

Execute query 2 and compare the result set with the highlighted records shown above. The actual query execution result should match with the above highlighted result set.

Applying the same theory for rest of the queries, you should be able to figure out the result set of each one.

Let's take query 3. Again I'm using the Cartesian product to determine the result set. This query will produce the records where InnerJoinTest1_PK is greater than or equal to the InnerJoinTest2_PK.

It is quite easier now to determine the result set if you consider the Cartesian product of two tables.

Let's look at the next two queries. (Query 4 &5) The Cartesian product below is shown the result set of both queries 4 and 5.
The green highlighted records are the output of query 4 where as query 5 will give you the combination of green highlighted and yellow highlighted records. Because the difference of query 4 and 5 is the '=' operator. So that the query 5 will output equal values of both columns in addition to the output of the query 4. The query 4 will produce the records where InnerJoinTest1_PK is less than the InnerJoinTest2_PK of the table 2.

The order of results from the actual execution of this query may be different with the Cartesian product. But it is not really a matter for our discussion. You use simply an ORDER BY clause to order the result set as you desire.

Finally consider the last query. (Query 6) This query will output the records where both columns are not equal. Again look at out most important Cartesian product shown below.

The green highlighted records show where both columns are not equal. Theoretically this result set is similar to Cartesian product - intersection. The intersection is the INNER JOIN result with equal operator. (Output of the query 1)


source http://www.sqlservercentral.com/articles/T-SQL/63322/

PIVOT working incorrectly?

By Ryan Clare

In some situations there may be a reason to total data alongside a column you want to pivot. In this example we will get a grand total of saverPoints that each salesperson earns with their sales as well as separating the number of sales by month.


create table #tmpPivotTest
(salesPersonsName varchar(25),
numberOfSales int,
monthAbv varchar(3),
saverPoints int)


insert into #tmpPivotTest values ('Yagnesh', 1, 'Jan', 1)
insert into #tmpPivotTest values ('Yagnesh', 2, 'Feb', 2)
insert into #tmpPivotTest values ('Yagnesh', 2, 'Feb', 2)
insert into #tmpPivotTest values ('Yagnesh', 2, 'Jan', 2)
insert into #tmpPivotTest values ('Jason', 3, 'Feb', 2)
insert into #tmpPivotTest values ('Jason', 1, 'Feb', 1)


--Shows how many sales each sales person gets per month
select salesPersonsName, [Jan], [Feb] from
(select salesPersonsName, numberOfSales, monthAbv from #tmpPivotTest) as SourceTable
PIVOT (sum(numberOfSales) for monthAbv in ([Jan],[Feb]) )as PivotTable


salesPersonsName Jan Feb
------------------------- ----------- -----------
Jason NULL 4
Yagnesh 3 4


This basic pivot works flawlessly. When we try to add another column to the select we don't get everything.


--Try to get the saver points as well
select salesPersonsName, [Jan], [Feb], saverPoints from
(select * from #tmpPivotTest) as SourceTable
PIVOT (sum(numberOfSales) for monthAbv in ([Jan], [Feb]) )as PivotTable

salesPersonsName Jan Feb saverPoints
------------------------- ----------- ----------- -----------
Jason NULL 1 1
Yagnesh 1 NULL 1
Jason NULL 3 2
Yagnesh 2 4 2


We are missing some of the saver points in here and it splits the sales person with each distinct value of saverPoints. For some reason pivot isn't working correctly! It's actually losing data! Lets try to sum the values together again...

select salesPersonsName, sum([Jan]) as Jan, sum([Feb]) as Feb, sum(saverPoints) as saverPoints from
(select * from #tmpPivotTest) as SourceTable
PIVOT (sum(numberOfSales) for monthAbv in ([Jan], [Feb]) )as PivotTable
group by salesPersonsName

select salesPersonsName, sum(saverPoints) as saverPoints from #tmpPivotTest group by salesPersonsName

salesPersonsName Jan Feb saverPoints
------------------------- ----------- ----------- -----------
Jason NULL 4 3
Yagnesh 3 4 3

salesPersonsName saverPoints
------------------------- -----------
Jason 3
Yagnesh 7

The saver points are still missing. The saver points are ignored since they are the same on both rows. If we change the rows ever so slightly we can get the correct answer.

select salesPersonsName, sum([Jan]) as Jan, sum([Feb]) as Feb, sum(saverPoints) as saverPoints from
(select *, newid() as superDecombobulatorV2
from #tmpPivotTest) as SourceTable
PIVOT (sum(numberOfSales) for monthAbv in ([Jan], [Feb]) )as PivotTable
group by salesPersonsName

select salesPersonsName, sum(saverPoints) as saverPoints from #tmpPivotTest group by salesPersonsName

salesPersonsName Jan Feb saverPoints
------------------------- ----------- ----------- -----------
Jason NULL 4 3
Yagnesh 3 4 7

salesPersonsName saverPoints
------------------------- -----------
Jason 3
Yagnesh 7

After being frustrated with pivot not working "correctly" and finally looking at books online with a cool head. We find that it is working according to the documentation in books online. From books online:

The following is annotated syntax for PIVOT.
SELECT <non-pivoted column> ,
[first pivoted column] AS <column name> ,
[second pivoted column] AS <column name> ,
...
[last pivoted column] AS <column name>
FROM
( <SELECT query that produces the data> )
AS <alias for the source query>
PIVOT
(
<aggregation function>( <column being aggregated> )
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column] , [second pivoted column] ,
... [last pivoted column] )
) AS <alias for the pivot table>
<optional ORDER BY clause>

Notice it only has one non-pivoted column and each value of the pivoted column listed. If I'd of had been level headed instead of trying to force pivot to work I would have just done two queries. One to get the pivoted data into a temp table, and the other to get the summed data and update the temp table. So let the lesson be learned. You can sometimes force your way through "incorrect" things in SQL Server, but it may just be easier to calm down and read the documentation.

PS: Don't forget to clean up

drop table #tmpPivotTest


source : http://www.sqlservercentral.com/articles/pivot/62808/

What is the difference between Server.Transfer and Response.Redirect methods?

You can transfer current users page request to another page with two methods:

* Server.Transfer (HttpServerUtility.Transfer Method)
* Response.Redirect (HttpResponse.Redirect Method)

Its not always clear how these two approaches differ so let us try to clarify things a little:

Response.Redirect sends HTTP code 302 down to the users browser along with the new URL location of the wanted page.
HTTP Code 302 actually means ' The requested resource resides temporarily under a different URI'.
After browser receives this code it tries to open the new location of the resource that was suggested by the server.
This actually causes two requests to the server, first one to the original URL, and second to the new URL that is suggested via 302 response.
All the Query Strings and Form variables are lost during the redirect and they are not available to the redirected URL.

Also its important to say that the new URL can reside on the same server but also it can be on some other server and the redirected URL does not need to be .aspx page it can be regular HTML page also).

So we can us the Redirect method to redirect users request to another page on our server like this:

Response.Redirect("newPage.html");

or to redirect our it to some other server like this:

Response.Redirect("http://www.someotherserver.com/newPage.aspx");


In contrast to all this when we call Server.Transfer we do not initiate another request to the server, but the original request is simply rewritten and transfered to some other page on the same server.
(This off course means that we can use it only to transfer requests to the pages on the same server, not to some other servers and we can only transfer to .aspx pages and not other page types like HTML, php etc).

All posted Form variables and query strings can optionally remain available to the second Page where we transfered request (if we use second overload Server.Transfer(string path, bool preserveForm) and supply true for the second parameter).
Otherwise the Form Variables and Query String are cleared just like when we use Redirect.
WARNING: If you use this method to preserve Query String and Form variables and receive error: "View State Is Invalid" its because your EnableViewStateMac attribute of the element is set to true. More on this error on this page: PRB: "View State Is Invalid" Error Message When You Use Server.Transfer

Its also important to note that because of the way Server.Transfer works, after the transfer, the URL shown in the users Web Browser remains the original one that was requested, because browser has no knowledge that its request was transfered (transfer occurs on the server side).

TIP: One thing to be careful about when using the Server.Transfer is to clear the the HttpResponse object with Response.Clear method on the transfered page to avoid any output from the first page to be shown on the second page.

So now that we know what are the similarities and differences between these two approaches we can try to use them wisely.

Here is the summary:

Response.Redirect should be used when:

* we want to redirect the request to some plain HTML pages on our server or to some other web server
* we don't care about causing additional roundtrips to the server on each request
* we do not need to preserve Query String and Form Variables from the original request
* we want our users to be able to see the new redirected URL where he is redirected in his browser (and be able to bookmark it if its necessary)

Server.Transfer should be used when:

* we want to transfer current page request to another .aspx page on the same server
* we want to preserve server resources and avoid the unnecessary roundtrips to the server
* we want to preserve Query String and Form Variables (optionally)
* we don't need to show the real URL where we redirected the request in the users Web Browser

How to programmatically Encrypt and Decrypt sections of web.config file?

Asp.Net 2.0 has built-in support for encryption and decryption of configuration file sections
with the .Net 2.0 Configuration API.

There are various sections of typical web.config file that usually contain sensitive information:

* - username and password used to connect to databases
* - usernam and password needed for runtime impersonation of fixed identity
* - your smtp server username and password
* etc

These important parts of web.config files are ideal candidates for encryption, especially if your website is hosted on
shared-hosting.

By using the built-in Encryption methods we can protect those configuration sections, so even if someone somehow manages
to obtain our web.config files or just take a brief look at them , there will not be much for him to see there.

The fun part is that those encrypted sections are automagically decrypted on-the-fly when our web application reads them,
so no change in application code is needed to be done after we protect web.config!

ASP.NET 2.0 comes with two encryption providers we can use to protect our configuration files:

* DataProtectionConfigurationProvider
* RSAProtectedConfigurationProvider
* also you can implement your own Providers, because its a plugin based model

The DataProtectionConfigurationProvider uses Windows Data Protection API (DPAPI) and this provider
uses a amchine-specific secret key for encryption and decryption.
This means that when you encrypt some data on one server, you can only decrypt it on the same server.

If you need to move configuration files with encrypted sections from server to server or if you have a Server-Farm
you will need to use the RSAProtectedConfigurationProvider. This provider uses RSA public key protection,
and you can use command line tool aspnet_regiis to create, import and export keypairs for encryption.

more on that on this link: http://msdn2.microsoft.com/en-us/library/ms998283.aspx

We will consider the simpler case where we use a machine specific DataProtectionConfigurationProvider
to programmatically encrypt our configuration files.
We pass the DataProtection provider name as a string to the ProtectSection method to protect our config sections.

here are two simple methods to protect and unprotect your config files sections.

using System.Web.Configuration;

public void EncryptSection(string sectionName, string provider)

{

Configuration cfg = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);


if (cfg == null)

return;

ConfigurationSection section = cfg.GetSection(sectionName);

if (section == null)

return;

if (!section.SectionInformation.IsProtected)

{

section.SectionInformation.ProtectSection(provider);

cfg.Save();

}

}



public void DecryptSection(string sectionName)

{

Configuration cfg = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);

if (cfg == null)

return;



ConfigurationSection section = cfg.GetSection(sectionName);

if (section == null)

return;



if (section.SectionInformation.IsProtected)

{

section.SectionInformation.UnprotectSection();

cfg.Save();

}



}

and here is an example on how to use these methods to encrypt/decrypt conectionStrings section of your web.config file:

EncryptSection("connectionStrings","RSAProtectedConfigurationProvider");
and

DecryptSection("connectionStrings");

or using the DataProtectionConfigurationProvider:


EncryptSection("connectionStrings","DataProtectionConfigurationProvider");
and

DecryptSection("connectionStrings");


To be positive that our configuration file is indeed protected, here is a code that
reads the web.config file from website root using server's FileSystem IO and
displays it in the TextBox component on ASP.NET page:
(we must use the IO commands to read the web.config file to see if its encrypted,
because if we would just read the settings directly from our web application they
would be decrypted on the fly and we would receive the real values).
When using FileSystem IO functions to read the web.config file we receive its true
encrypted contents:


using System.IO;


public void ShowWebConfigFile()

{

StreamReader sr = File.OpenText(Path.Combine(Request.PhysicalApplicationPath,"Web.Config"));

string contents = sr.ReadToEnd();

sr.Close();

webconfig.Text = contents;



TextBox1.Text = WebConfigurationManager.ConnectionStrings["local"].ConnectionString;

}

and here is how our connectionStrings section should look when read this way:




AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAevxuCHpsWkK7mJZZB5u9DQQAAAACAAAAAAADZgAAqAAAABAAAADTY4+
89xTx8n6fgJu3M4AEAAAAAASAAACgAAAAEAAAAJj77evc30ixlYD7sXdZyUbIAAAATK3ZOyiXebQMhT7KOT1TpRDqPxd/zeD2uwg0
4LeC7CHmMquRKcXGI5Xb7LJnNKysbfXycu7ZmUgd4i7mp3ly/unc/FVWG9PAk6b5LG8BTsdNzTQeZYT7u8Iap2BDFKPgiZqLA4SvM
ti1MyjAK6P+lQCp0irWiE2yqWpMiQ82+k1SZj6AdYjNvYLmoAPFzqX1++ybMdcHueXsv4dvd5Fw2fFTh6iiBdR2xQtvgMrtP9OHmm
CTM1IJ6G8Jv0oxqG7J1b3JWQSIs9MUAAAA4/q1tlT5sNGslF4/yogmvKEz4MY=

Put your ASP.NET 2.0 application Offline - the user friendly way!

Every web application has its Life Cycle. This cycle involves (among many other things) developing, deploying, debugging, maintenance etc.

When your web application is already deployed on production server, making some critical changes on it can be a challenging
task if you have multiple visitors online.

Developers at Microsoft added one nice feature to ASP.NET 2.0 that addresses this very problem:

if you upload a html file named "app_offline.htm" to the root of your website, ASP.NET recognizes this file, shuts down your application, and for every next requested page, contents of your "app_offline.htm" file is server instead.

When you are done updating your website, you can delete this file or just rename it, and your website will start working again

NOTE:
One thing to be careful about: It is recommended that your "app_offline.htm" file size is minimum 512 bytes in order to be shown correctly in Internet Explorer 6.
(This is because IE6 has setting called "Show Friendly Http Errors" that shows its own error messages instead of the pages returned from web server - if they have HTTP status code different than 200 and if they are smaller than 512 bytes in size).

Introduction to Language Integrated Query (LINQ)

ProblemLanguage-Integrated Query (LINQ) is a groundbreaking innovation in Visual Studio 2008 and the .NET Framework version 3.5 that bridges the gap between the world of objects and the world of data. As LINQ is part of the development enhancements in SQL Server 2008, how can I have an understanding of how it works and how I can use it in other areas of administration, not just SQL Server?
SolutionLet's start off by explaining LINQ. LINQ is a codename for a project which is a set of extensions to the .NET Framework that encompasses language-integrated query, set and transform operations. It extends C# and VB with native language syntax for queries and provides class libraries to take advantage of these capabilities, available only in .NET Framework 3.5. For developers who write code that regularly access a recordset, this means a lot. The fact that queries are usually expressed in a specialized query language for different data sources makes it difficult for developers to learn a query language for each data source or data format that they must access. This is what LINQ is all about. It simplifies data access by providing a consistent model for working with data across various kinds of sources and formats. In LINQ, data is translated into objects, something that developers are more comfortable with working . Understanding LINQ will give us an idea of its capabilities and its benefits
Create a simple LINQ project
Let's start by creating a simple console project using the C# language in Visual Studio 2008. You can also download the free Visual C# 2008 Express Edition from the MSDN Download Center. Make sure you select .NET Framework 3.5 from the target framework drop-down menu.
This will open up your Program.cs file. Notice that by simply creating a project that targets the .NET Framework 3.5 automatically adds a using directive for the System.Linq namespace as this is already a part of the System.Core assembly. The System.Linq namespace provides classes and interfaces that support queries that use LINQ. We will start with this to understand the basics of LINQ.
Let's start writing some code inside the static void Main(string[] args):
//Obtaining the data source string[] carNames = {"Ferrari", "Porsche", "Mercedes" , "McLaren", "Audi", "BMW"}; // Create the query // query is an IEnumerable var query = from name in carNames where name.Contains("e") select name; // Execute the query foreach (string name in query) { Console.WriteLine(name); }
//Pause the application Console.ReadLine();
We'll examine the basic components of a LINQ query. Any LINQ query consists of three distinct actions. These are obtaining the data source, creating the query and executing the query. The first thing that we need to do is to have a data source. In this case, it's an array of strings which supports the generic IEnumerable(T) interface. This makes it available for LINQ to query. A queryable type does not require special modification to serve as a LINQ data source as long as it is already loaded in memory. If not, you would have to load it into memory so LINQ can query the objects. This is applicable to data sources like XML files. Next, is the query. A query specifies information to retrieve from the data source. This is similar to a SQL query which includes syntaxes like SELECT, FROM, WHERE, GROUP BY, etc. Looking at the code above, you'll notice that its not like your typical SQL statement as the FROM clause appeared before the SELECT clause. There are a couple of reasons for this. One, it adheres to the programming concept of declaring the variable before using it. Also, from the point of view of Visual Studio, this makes it easy to provide the IntelliSense feature using the dot (.) notation as the variable has already been declared and that the framework has already inferred the correct type to the object. This provides the appropriate properties and methods, making it easy for the developers to write their code.
Let's look at how the code was constructed. The from clause specifies the data source, in this case, the carNames collection. The where clause applies the filter, in this case, the list of all elements in the collection containing the letter 'e'. The select clause specifies the type of the returned elements. This means that you can create an instance of the elements in your collection. An example could be creating an instance of an object with fewer attributes. The query variable, query, just stores the information required to produce the results when the query is executed, maybe at a later point. Simply defining the query variable does not return any data nor takes any action. The third component of the code above is query execution. As I mentioned, the query variable does not contain any data, but rather simply contains only the query commands. The actual execution of the query is when we iterate over the query variable. There are a couple of ways to do this. One of which is shown above. The use of a foreach statement iterates thru the query variable and execute it as well. This concept is called deferred query execution. This is very much important when dealing with data sources such as highly-transactional database systems as you minimize connecting to the database unless necessary (database connections are additional resources on the database server as well). You can opt to execute the query immediately by using aggregate functions such as Count, Max, Average and First or calling the ToList() or ToArray() methods. Another way is to bind the collection to a data-bound control in either a web or windows form control similar to how you would do it in previous versions of Visual Studio - specifying the DataSource property of the control to be the query variable and calling the DataBind() method.
Another area to highlight in the code is the use of the keyword var, which is a new keyword introduced in C# 3.0. What this does is it looks at the value assigned to the variable, then determines and sets the appropriate one. This concept is called type inference. From the code above, the query variable, query, appears to be an array of string. So the compiler will automatically assume that it is a variable of type IEnumerable. This is helpful if you do not know the variable type during runtime. But this does not mean that any type can be assigned to the variable after the initial assignment - something like a dynamic type - since .NET is a strongly typed language platform. This simply means that an object can take on a different type and the compiler can simply handle that. Assigning a different type to an already existing one violates the concept of polymorphism in object-oriented programming. Let's say you assign the value 12 to the query variable, query. This will throw a type conversion exception as the original type of the variable is a string collection.
Your output will look like this when you run your project in Visual Studio. You can press F5 or click on Debug - Start Debugging in Visual Studio
Next Steps
You have seen how powerful LINQ queries are and how similar they are to SQL queries. There are other data access methods that LINQ implements including
LINQ to SQL
LINQ to XML
LINQ to Objects
LINQ to Entities
LINQ to DataSets
Give this example a try and change the query parameters so you can have a feel of how LINQ works.
Have a look at the 101 LINQ Samples at the MSDN Visual C# Developer Center.
For samples in Visual Basic, you can check the Getting Started with LINQ in Visual Basic site from MSDN

source : http://www.mssqltips.com/tip.asp?tip=1502

Power Of Empati

Anda tidak akan tahu dimana besok, minggu depan atau bulan depan berada. Sekarang mungkin anda sedang asik memainkan remote televisi anda sambil menikmati long weekend anda. Tapi terbayangkah oleh anda bahwa senin besok dikarena satu dan lain hal anda haus di pindahkan ke propinsi baru? Mungkin di Aceh atau mungkin daerah Paapua? Hal yang baru bukan? Dan mungkin ini menjadi suatu tantangan tersendiri bagi anda. Berat menantang tapi itulah hidup, dimana semuanya akan selalu berubah dan berganti. Dan satu yang pasti walaupun dunia berubah, namun anda akan dan harus tetap struggling. How to survive and struggling to every condition, empaty is the solution for this.
Empati atau yang secara gamblang sering dibilang berpikir seperti orang lain (how to thinks like others), terlebih pihak lain disaat kita berkomunikasi atau berkerja dengan orang lain. Mengerti orang lain dan memahami orang lain akan memudahkan anda untuk bekerja dan berkembang dimanapun anda berada. Empati sudah menjadi ilmu dasar bagi kita, cuma terkadang kita terlalu sombong untuk mau dan berusaha mengerti orang lain. Padahal anda tahu bahwa ilmu dan kepintaran anda memahami orang lain merupakan senjata anda untuk berperang agar survive dan maju dalam kehidupan anda.
Bila anda mau menyisihkan sedikit waktu anda dan melihat di sekitar anda, Pelajarilah! Bahwa belum tentu orang yang pintar itu akan lebih mudah berhasil dibandingkan dengan orang-orang yang memiliki kemampuan untuk bisa berempati dan bekerja sama dengan orang lain. Kenapa demikian? Hal yang menarik bukan? Hal ini terjadi karena orang yang memiliki kemampuan berempati lebih mampu untuk memuaskan orang lain yang bekerja sama dengannya. Tentu saja anda akan bisa menyenangkan orang lain pada saat anda mengetahui apa yang dibutuhkan orang lain dan memenuhi kebutuhannya. Bagaimana anda bisa mengetahui orang lain, inilah the power of empathy. Dia mampu membawa anda untuk menyenangkan orang lain. Dan bila anda mampu untuk melakukan hal ini maka semua orang akan berusaha bekerja dengan anda dan ingin merasakan kepuasan berkerjasama dengan anda. Melalui inilah orang akan datang kepada anda dan membukakan pintu-pintu kehidupan baru (kesempatan) untuk anda.
Untuk membantu anda dalam memahami kekuatan empati ini, saya akan membawakan kepada anda salah satu cerita menarik dari buku yang saya baca yaitu “How to Get Rich” karangan Donald J. Trump. Di dalam buku ini dia bercerita bahwa salah satu temannya Wayne Newton sedang mengalami masalah dengan para bangkirnya. Di mana dia tidak bisa membayar hutang-hutangnya kepada para bangkirnya. Lalu Wayne menghubungi Trump dan bertanya apa yang harus dia lakukan untuk menghadapi masalahnya ini. Lalu trump menjawab “Wayne, aturlah pertemuan dengan mereka. Idealnya makan malam dengan mereka dan keluarganya dan kenali mereka, maka akhirnya mereka akan menyukai anda.
Tiga minggu kemudian setelah pembicaraan itu Wayne menelepon Trump dan mengatakan ia telah makan malam dengan ketiga banker dan berkata bahwa mereka adalah orang-orang teramah yang pernah ia temui, dan sebagai hasilnya bank-bank tempat Wayne berhutang mereschedule hutang-hutang Wayne dan membuat Wayne mampu untuk survive sampai sekarang.
Anda lihat bukan bahwa empati memiliki power yang sangat besar di dalam kehidupan anda. Semua hal bisa anda hadapi bila anda tahu dengan siapa anda berhadapan, dengan siapa anda berkomunikasi dan bagaimana mereka mengharapkan anda memperlakukan mereka. Jadi pelajarilah sifat dan kepribadian mereka, maka mereka akan mengantarkan anda ke pintu-pintu kesempatan yang mereka miliki.
Buat mereka suka dan percaya kepada anda, maka mereka tidak akan ragu untuk memberikan kesempatan kepada anda.
Salam Sukses!
D_LoebizFounder & Moderator Beranie Gagal

Free Tools for the SQL Server DBA

We've all done it.

Scourer the Internet for a script or free tool to help do our job or solve a problem. SQL Server Central and similar sites contain an abundance of scripts and guides to assist us. There are times when I want to use a tool instead of a collection of scripts and commands. A tool more often than not requires less customization than a script and sometimes less technical know how to get them to work. So I decided to share a list of free tools that I use from time to time.


NVU

To start with I wrote this article using NVU an open source WYSIWYG HTML editor. It creates HTML pages with very simple HTML code. It still adds a few extra commands such as " " but that's nothing compared to what MS Word generates for HTML code.
NVU is open source software and does not require any registration to download or use.



FullSun SQL Server Toolkit

It is a nice tool kit with many helpful features. Currently this tool is for SQL Server 2000 but a 2005 version is being developed. Here are some of the functions available with this tool:
List all SQL ids with very easy passwords so they can be fixed.
Review all the errors or failed logins in the SQL Error Log files.
Monitor database growth through their backup file sizes along with viewing the current table sizes.
Check nine predefined performance counters.
Read a database log file. (This was primary reason I got this tool. It's nothing fancy but I like it.)This tool is CharityWare software. Every time the tool is started, a panel pops up in the background with instructions on using the tool and a request for a charitable donation.


EMS SQL Manager for SQL Server Lite

EMS provides a free lite version of their SQL Manager tool with limited administration functions. The SQL Monitor is fairly simple and only works on SQL Server 2005. The feature I like most about this tool is when I am browsing or editing data in a table, I can view the data in a grid or form. The form shows the contents of one row vertically. This is very helpful for reading long columns.To download from the EMS website you need to register. There are two download options. One is a full download of the product and the other is just the executable. So far I've only used the 2.73 MB executable because it does not require any installation. The full download of this tool is available from an alternate site:

http://www.download.com/3001-10255_4-10594854.html


Toad for SQL Server

Quest has a freeware version of their toad for SQL Server. I downloaded it because I heard how popular Toad for Oracle was. It seems to have all the basic functions of Enterprise Manager. The SQL Editor is incorporated into the tool as a separate tab. Yes, it uses tabs to provide access to the various functions. The export wizard supports a variety of formats. It does disappoint me that you need the commercial version to export a table as insert statements. You would start to think they are in the business of making money. Wait, I guess they are.Quest does not seem to require any registration to download and use this tool. Previously it required you to download the product every 60 days but that restriction has been removed for the current release.


SQLCheck

SQLCheck is a free performance dashboard from Idera. It provides real time monitoring of about a dozen key performance indicators such as Buffer Cache Ratio, User Connections, and Processor Time. In addition, you can review SQL processes, SQL error log, jobs, and configurations. All this information is available using other tools but this tool provides a single location to finding this information.Idera requires you to register before you download the product. You will probably get a phone call from the company, but you won't be continually spammed.


WinSql

Synametrics provides a free lite version of their WinSql product. It is an ODBC SQL Query tool with create table options. I work with some developers who love using it. Since developers use it, I use it to help troubleshoot any problems they are having executing SQL statements.
To use this product you need to register.


SQLinForm

SQLinForm is a free online SQL Formatter for many database engines. This tool is great for cleaning up complex SQL and making it more readable. There are other sites with free online SQL formatting tools (like SQL Parser). When one parser has trouble formatting a SQL statement you can try it on another site.These tools are free to use online with no registration required.SpaceMonger It happens, a drive is running out of space. To get a quick view of what is taking up the space, I use SpaceMonger v1.4.0. It provides a bird's-eye, graphical view of the file allocations on a single drive. Plus it is faster than some similar tools. The tool consist of a single 212k executable that can be copied to servers and executed locally to create the report as fast as possible. When needed, it can be used remotely on mapped drives.
Sixty-Five has released a purchasable version of this tool that contains more functions. They are still providing the version 1.4.0 of SpaceMonger at no cost.



This free version does not require any installation or registration to download and use.


ExamDiff

There are times when I need to compare SQL code, stored procedures, table counts, and other data. For this I use ExamDiff. It has a clean GUI interface for selecting files and browsing the results. While browsing the results, it has an option to edit the files. The company PrestoSoft provides a more advance version, but for simple text file comparisons the free version is adequate.This program does not require any installation or registration to use. Just download the zip and click on the executable.


Windows Sysinternals

Mark Russinovich and Bryce Cogswell at Sysinternals created a large collection of free tools to help troubleshoot and diagnose window servers and desktops. In 2006 Microsoft purchased Sysinternals. Most of these tools are still free and continue to be updated. I am a big fan of the tools Process Explorer and Handle. When I have trouble with a locked file, I turn to these tools. Most of tools are executables and require no installation.Last I knew they still do not require registration to use.


7-Zip

7-Zip is a compression tool that supports multiple formats and files larger than 4 GB. WinZip v8 does not compress files larger than 4 GB. My company was not going to pay the $50 dollars for a newer version. Why? Because that's the way they are. So I found 7-zip which does support zip files > 4 GB. It is slower than WinZip 11 and does not include encryption but at least it gets the job done. I don't use the 7z format because it is really slow on big files even though the files it creates are suppose to 30-70% smaller than Zip.7-Zip is open source software and does not require any registration to download or use.



The 46 Best-ever Freeware Utilities

This site does not have any database specific tools. Instead it has 46 other categories of tools that are reviewed and rated by the site owner. So if you are looking for an FTP client, text editor, registry editor or some other tool give it a look.This site only provides links to other sites from which you can download the software.


Yahoo E-mail

Many companies released free tools that require registration. I use a free Yahoo e-mail account when I register. It provides an excellent spam filter. Yeah it's not 100% but it's sure better than some others I've had. Sometimes registration information gets caught as SPAM but as long as it goes to the Bulk (SPAM) folder you can retrieve it.Yahoo sometimes sends you an advertisement, but they are easily identified and deleted.


Conclusion
That's it for my list of DBA tools. Most of the time, purchased tools provide more functionality. But when it comes to free, these tools do fill a need. Last I checked all these tools where still available and free. If you know of any other useful free tools for a SQL Server DBA be sure to mention it in "Your Opinion" discussion for this article.



By David Bird,

source : http://www.sqlservercentral.com/articles/Miscellaneous/2959/

Undocumented Extended and Stored Procedures

Undocumented Extended and Stored Procedures
By Timothy A Wiseman
t

Introduction

There are several undocumented system stored procedures and extended stored procedures in SQL Server 2005 which can be of enormous use. Since they are undocumented, it can be risky to rely upon them. There is always the possibility that they will be dropped or altered, which can be worse, with the next version of SQL Server or even the next service pack. If it is important that the code in question works with the next version of SQL Server then it is wise to avoid undocumented procedures. But they can be more than worth that small risk when compatibility with future versions is not a priority.

XP_FileExist

The usage is:

EXEC xp_fileexist <filename> [, <file_exists INT> OUTPUT]

If executed without providing an output variable, it will display 3 columns indicating whether the table passed exists, whether it is a directory, and if the parent directory exists. For instance:

exec master.dbo.xp_fileexist 'C:\temp'

Results in this output:
xp_fileexist Output File Exists File is a Directory Parent Directory Exists
0 1 1

If it is executed with the output parameter it will set the value to one if the file exists and 0 otherwise. It will set the variable to 0 if the name given is a directory.

Generally, it is better to check validate the existence of any files prior to calling the T-SQL script, but there are certainly times when it is convenient to be able to do it from within a T-SQL script. This syntax is far more graceful than work arounds which can be achieved with xp_cmdshell.



SP_MSForEachDb

sp_msforeachdb will execute the attached string as a command against every database on the server. Any question marks (?), within the string will be replaced by every database name. It can be useful for finding the database which holds a given table on a server. For example:

exec dbo.sp_MSforeachdb 'select ''?'', * from [?].INFORMATION_SCHEMA.TABLES where table_name like ''authors'' '

will find every table named authors on the server. It can also be useful for running maintenance routines against every database. For instance, this:

exec dbo.sp_MSforeachdb 'DBCC SHRINKDATABASE(N''?'' )'

will shrink the file size of every database on the server. It may be worth noting that on a well designed production system it would not be a good idea to run that command, but it can be useful for reclaiming file space on test and development servers and illustrates the broader ways it can be used for administrative commands when needed.

SP_MSForEachTable

sp_msforeachtable is very similar to sp_msforeachdb, except executing the command against every table in the database. For instance, if there is a test database and all tables in the test database need to emptied while retaining the table structures, this command could be used:

exec dbo.sp_msforeachtable 'delete test.dbo.[?]'



SP_who2

Sp_who2 is like sp_who's bigger brother. It functions in a very similar fashion to the documented procedure sp_who except that some of the column names are slightly changed, the ecid column is dropped, and some additional columns are listed. It adds the cputime, diskio, lastbatch, and programname columns to the information provided by sp_who. Its output appears as:


sp_who2 output SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID
1 BACKGROUND sa . . NULL RESOURCE MONITOR 100 0 04/12 15:58:54 1 0
2 BACKGROUND sa . . NULL LAZY WRITER 10 0 04/12 15:58:54 2 0
3 SUSPENDED sa . . NULL LOG WRITER 0 0 04/12 15:58:54 0 0
4 BACKGROUND sa . . NULL LOCK MONITOR 0 0 04/12 15:58:54 4 0


Both sp_who and sp_who2 with its added columns can be tremendously valuable in scripts and programs, but for use on an ad hoc basis it is often more convenient to invoke the Activity Monitor GUI.

sp_MSdependencies

sp_MSdependencies is a powerful procedure that can be used to determine all dependencies for an object within a database. Its usage is:

exec dbo.sp_MSdependencies [<object name> ] , [<object type>], [<Flags>], [<objlist>]

The procedure also accepts an integer variable @intrans, but it does nothing. All of the variables are optional and in most cases it is not necessary to pass both an object name and an object type. If

exec sp_msdependencies '?'

is executed it will provide a brief description of the usage options.

sp_MSdependencies is similar to the documented stored procedure sp_depends, but it is simultaneously more flexible and more difficult to use. sp_depends will return two result sets, the first lists those things that the targeted object depends on directly and the second listing those things which depend directly on the targeted object, and in both cases it does not consider foreign key relationships to be dependencies. sp_MSdependencies on the other hand does count foreign key relationships and will return either things which depend on the target or on which the target depends and precisely what it returns is determined by the flags passed. By default, it will not return system tables or other internal objects or user defined data types, but it can be set to do so by using the flags switch. It also formats its results different from sp_depends.

If it is executed with only an object name, it will return everything on which that object depends. For instance:

exec sp_msdependencies 'dbo.titleview'

exected in the pubs database returns
sp_msdependencies output oType oObjName oOwner oSequence
8 authors dbo 1
8 publishers dbo 1
8 titles dbo 2
8 titleauthor dbo 3

Which indicates that dbo.titleview depends on authors, titles, and titleauthor while authors depends on the publishers table. The oType of 8 indicates that they are all tables.

Providing only an object type by number will result in a list of everything which depends directly or indirectly on any object of that type within the current database. In the sample pubs database, the dbo.titleview is the only view, so if views were selected and

exec sp_msdependencies NULL, 2

were executed the results would be

oType oObjName oOwner oSequence
----------- ------------------------ ------------------------------- ---------
8 authors dbo 1
8 publishers dbo 1
8 titles dbo 2
8 titleauthor dbo 3
4 titleview dbo 4

Providing everything which any view depends on, including the view itself.

The flags can be added to either an object name or an object type and they determine precisely what is returned. The flags paramater is a bitmap expressed in hexadecimal. It permits precise control over what types of objects are returned and will allow the return of children, or objects which depend on the target, instead of parents, or objects on which the target depends. Using the flags, it can be set to return the system objects and user defined data types which are normally skipped. It can also be set to return only first level relationships which makes it more like sp_depends. For instance, to retrieve the objects which depend on the titles table execute:

exec sp_msdependencies 'titles', NULL, 0x401fd

which returns:
sp_msdependencies output oType oObjName oOwner oSequence
8 roysched dbo 1
8 sales dbo 1
8 titleauthor dbo 1
16 reptq1 dbo 1
16 reptq2 dbo 1
16 reptq3 dbo 1
4 titleview dbo 2
16 byroyalty dbo 2

Everything on the list depends either directly or indirectly on the titles table. For instance, the procedure byroyalty does not directly rely on the titles table, but it relies on titleview which in turn is dependant on the titles table.

In general, it is more convenient and user friendly to simply use the GUI provided in the SSMS to gather the dependencie information when it is needed on an ad hoc basis, but sp_msdepencies can be invaluable in creating scripts in which dependencies need to be evaluated. Its ability to consider foreign key relationships and indirect dependencies can also make it valuable in situations where the documented sp_depends is not appropriate.

Conclusion

Undocumented procedures should always be used cautiously, especially when dealing with procedures that are expected to be able to migrate from one version of SQL server to another. Undocumented procedures are far more likely than their documented counterparts to be changed, and they will not be officially supported if technical support is ever required. With those risks in mind, they can often be tremendously useful. This provides a short list of some of the more useful ones and how they can be used in SQL Server 2005.
By Timothy A Wiseman

http://www.sqlservercentral.com/articles/Stored+Procedures/62868/

Ciptakan Kehidupan, Bukan Sekedar Hidup

Your successes and happiness are forgiven you only if you generously consent to share them. ?Kesuksesan dan kebahagiaan akan sangat berarti jika kau mau berbagi dengan orang lain." Albert Camus

Untuk dapat sekedar hidup, mungkin kita tidak perlu bersusah payah mencari peluang ataupun memikirkan bagaimana meningkatkan kualitas dan manfaat diri kita. Namun sebagai mahluk yang paling spesial diantara mahluk ciptaan Tuhan YME, kita berkewajiban untuk mendapatkan kehidupan yang berarti. Kita harus berupaya semaksimal mungkin. Sebuah pepatah bijak menyebutkan, "Find a meaningful need and fill it better than anyone else. Kejarlah sesuatu yang bermakna, dan gunakanlah setiap peluang yang ada secara lebih baik dari siapapun."

Ada beberapa langkah untuk menjadikan kehidupan kita menjadi lebih berarti.

*Langkah pertama adalah memperbesar kemauan untuk belajar.

Manusia mempunyai pikiran yang luar biasa, maka gunakan pikiran tersebut untuk belajar menciptakan kemajuan-kemajuan dalam hidup.

Kita dapat belajar dari berbagai hal, diantaranya adalah belajar kepada pengalaman hidup, kegagalan, kejadian sehari-hari, orang lain dan sebagainya. Maka tingkatkan terus kemauan
belajar.

*Langkah kedua supaya kehidupan kita lebih berati adalah mencoba melakukan sesuatu agar lebih dekat dengan impian yang diidamkan.

Bekerjalah lebih keras, lebih aktif atau produktif. Langkah ini sangat efektif dalam meningkatkan kemungkinan mendapatkan uang, kekayaan atau segala sesuatu yang berharga bagi manusia.

Satu hal yang patut dijadikan pedoman bahwasanya kerja keras itu bukan semata-mata mengejar 5 P, yaitu power (kekuasaan), position (posisi), pleasure (kesenangan), prestige (kewibawaan) dan prosperity (kekayaan).

Setiap usaha yang hanya berorientasi kepada lima hal tersebut memang menjamin kesuksesan atau bahkan hasil yang melimpah ruah, tetapi tidak menjamin sebuah akhir yang menyenangkan. Contohnya adalah sebuah fakta tentang delapan orang miliarder di Amerika Serikat yang berkumpul di Hotel Edge Water Beach di Chicago, Illionis pada tahun 1923. Mereka adalah orang-orang yang sangat sukses, tetapi mengalami nasib tragis 25 tahun kemudian.

Salah seorang diantara mereka adalah Charles Schwab, CEO perusahaan besi baja ternama pada waktu itu, yaitu Bethlehem Steel. Tetapi Charles Schwab mengalami kebangkrutan total. Sehingga ia terpaksa berhutang untuk membiayai hidupnya selama 5 tahun sebelum meninggal.

Yang kedua adalah Richard Whitney, President New York Stock Exchange.
Namun pria ini ternyata menghabiskan sisa hidupnya dipenjara Sing Sing. Orang ketiga adalah Jesse Livermore, raja saham "The Great Bear" di Wall Street. Tetapi Jesse mati bunuh diri.

Orang ke empat adalah "The Match King", Ivar Krueger, CEO perusahaan hak cipta, yang juga mati bunuh diri. Begitu juga dengan Leon Fraser, Chairman of Bank of International Settlement, ia mati bunuh diri.

Yang keenam adalah Howard Hupson, CEO perusahaan gas terbesar di Amerika Utara. Tetapi ia sakit jiwa dan dirawat di rumah sakit jiwa hingga akhir hidupnya. Arthur Cutton sebelumnya adalah pemilik pabrik tepung terbesar di dunia, tetapi ia meninggal di negri orang lain.

Sedangkan Albert Fall, waktu itu ia adalah anggota kabinet presiden Amerika Serikat. Namun ia meninggal di rumahnya di Texas ketika baru saja keluar dari penjara.

Di dunia ini tidak sedikit orang yang semula sangat sukses, tetapi merana di tahun-tahun terakhir kehidupan mereka.

Kehidupan mereka seakan-akan tidak berarti meskipun sebelumnya sangat kaya raya. Upaya terbaik memang dapat menghasilkan kesuksesan besar, tetapi bukan berarti merupakan jaminan sebuah akhir kehidupan sebagai manusia yang penuh arti.

Karena itu langkah berikutnya yang harus kita lakukan adalah mengimbangi kerja keras dengan berbuat kebaikan.

Seorang penulis pada abad 20-an yang berkebangsaan Perancis, Andr?Gide, mendefinisikan kebaikan itu sebagai berikut; "True kindness presupposes the faculty of imagining as one's own the suffering and joys of others. ?nbsp; Kebaikan yang sesungguhnya adalah kemampuan merasakan penderitaan maupun kebahagiaan orang lain."

Kerja keras yang diimbangi dengan berbuat kebaikan akan menghasilkan semangat yang tinggi untuk mendapatkan lebih dari apa yang dibutuhkan. Hal itu terdorong oleh keinginan untuk dapat berbagi kebahagiaan dengan orang lain. Pada akhirnya kebaikan tersebut berpengaruh positif terhadap semangat hidup, motivasi, dan kemajuan sikap dan ekonomi. James Allen, penulis buku berjudul As a Man Thinketh mengatakan, "Pemikiran serta perbuatan baik tidak mungkin mendatangkan hasil yang buruk; pemikiran dan perbuatan buruk tidak mungkin mendatangkan hasil baik."

Dengan belajar, bekerja keras dan berbuat kebaikan maka kita akan dapat menciptakan kehidupan yang jauh lebih berarti.

Langkah-langkah sebagaimana dijelaskan diatas terbukti juga sangat efektif menjadikan kesan positif tentang diri kita tidak mudah dilupakan orang. Saya meyakini bahwa kita masih mempunyai banyak kesempatan dan potensi untuk mendapatkan kehidupan berharga itu dimanapun dan apapun pekerjaan kita.

Sumber: Make A Life, Not Merely A Living - Ciptakan Kehidupan, Bukan Sekedar Hidup



By Andrew Ho

Upgrading SQL Server 2000 DTS Packages to SSIS

By Brian Knight
If you're like me and most companies, you probably have dozens if not hundreds of SQL Server 2000 DTS packages in you SQL Server environment. The idea of upgrading those packages to SQL Server 2005 SSIS can be daunting. The packages probably drive production loads and you've spent years stabilizing them and don't want that 4AM call. This article shows you your options for upgrading the packages automatically and what components won't be upgraded.
The Server and Workstation Upgrade

After you upgrade your SQL Server to SQL Server 2005, the packages carry over in place and are not upgraded as part of the process. All of your production jobs that call the packages are also carried over without change. Without touching the packages, you can continue to run the 2000 packages since the DTS runtime environment comes over. You'll be able to find your 2000 packages in the Microsoft SQL Server Management Studio under the Management node then Legacy -> Data Transformation Services. The problem is going to be that you'll need to install the SQL Server 2005 Feature Pack (specifically, the Package Designer for 2000) to modify the package after the upgrade. This was not installed by default to give you a smaller surface area in your install. After you upgrade the last node on your SQL Server, the SQL Server 2000 tools are completely removed.

Note: Support for the Metadata Repository has been removed in SQL Server 2005 so those packages will not come over. You will need to save those packages into a different storage area like MSDB or the file structure before upgrading your instance.

You can also use the SQL Server 2005 tools to connect to SQL Server 2000 databases to manage the server and packages. I've been using the 2005 tools since an early CTP to manage my entire SQL Server 2000 environment and have encountered very few issues even in beta. The problem will lie in if you chose not to upgrade your tools. You cannot manage or design against a SQL Server 2005 environment with the 2000 tools. In other words, the 2000 tools aren't forward compatible as in past releases of SQL Server.
Package Upgrade Options

The first step that I would recommend in your upgrade path is to run the SQL Server 2005 Upgrade Advisor. The Upgrade Advisor will report also against DTS packages. It will give you warnings and errors and identify specific packages that will cause you grief in the upgrade.

In SQL Server 2005 SSIS packages, you have a new task called Execute SQL Server 2000 Package task. Another way to inch into an upgrade is to create a SQL Server 2005 SSIS package with a single Execute SQL Server 2000 Package task that executes the old package until you can execute have time to upgrade each component. The task has the option to embed the 2000 package into the task itself so as you move the SSIS package, the DTS package comes with it. This tactic will be used later by the Upgrade Wizard for complex logic.

The best way to upgrade your packages is with the Package Upgrade Wizard. The wizard will leave the old package and calling jobs in place but clone the package with no job. The wizard isn't perfect though. It was developed to perfectly upgrade simple workflow and transforms written with the Import/Export Wizard or similar logic. If you have customized transforms like one that does a upper case transform, it will not be ported over exactly as is. If you have any complex transforms or tasks, a small 2000 DTS package that contains that step will be created and a Execute SQL Server 2000 Package task will be created to call that package. The package will be embedded in the task and you can modify the 2000 package by clicking Design Package inside that task (it won't show up in your legacy packages node). Using the wizard, I was able to upgrade about 75% of the packages (with simple logic) and 10% upgraded with issues like I just mentioned and the remainder had to have manual intervention.

There are some components that will not be compatible with SQL Server 2005. Just because they're not compatible though, doesn't mean there's not an upgrade path. The areas of special consideraExecute SQL Server 2000 Package tasktion are:

* Dynamic Properties task has no 2005 task to port to. A placeholder Script Task will upgrade over but will not have any functionality. Your package will not work as expected until you upgrade the logic using the new Package Configuration option or expressions.
* Analysis Services tasks are wrapped in a Execute 2000 Package task.
* ActiveX Script tasks that take extensive use of the DTS object model will not be supported. For example, if you used the model to call other packages or loop, this will not port. You will want to implement some of the built-in controls for this like the For Each Loop container.
* Global Variables are ported to package variables with no issues.
* Data Driven Query tasks are not supported. The upgrade wizard will use a sub-2000 package to make this work.
* Any custom task will be wrapped in a subtask as well.

It's important to note that long term support of SQL Server 2000 DTS is not in the cards. You need to spend this release of SQL Server 2005 getting off of DTS because I can't imagine support of DTS existing in the next release (code named Avalon). I hope some useful information to get you start down the path to upgrading your DTS packages to SSIS.

source : http://www.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/upgradingsqlserver2000dtspackagestossis/2201/

Measuring Performance of Stored Procedures

by Preethiviraj Kulasingham
Database developers need to write stored procedures which are not only fully functional, but also which perform acceptably. As database servers use permanent storage media heavily (mainly because of ACID properties), which are known for slow performance, optimizing the stored procedures for performance is very important. This article concentrates on some of the counters used to measure performance and analyses methods of capturing these counters. This article is intended for database developers who write stored procedures and optimize for performance.

Currently there are three counters widely used for measuring performance of the system.

* Execution time
* CPU Cost
* IO Cost

Execution Time

The most primitive method is to get the time taken to execute the query from SQL Server Management Studio (SSMS). The status bar displays the time taken in terms of hour, minute and second. This may be a measure used when a query takes a longer time (Usually more than 10 seconds, so that a 10% improvement to the query could be measured) and the time difference in sub seconds is insignificant. When a query executes within a second, SSMS rounds the value. Thus this value could not be used to get the value if the query executes within sub seconds.

Another method is to get the system time before and after the execution of the stored procedure and analyze the difference. SQL Server management studio could be used for this purpose, simply by adding print statements before and after the stored procedure.

PRINT CONVERT(varchar, GETDATE(), 114)

The developer could also use variables to hold the values and calculate the time difference.

The advantage over this method is it could be used within a query too. For a stored procedure to be executed multiple times with different parameter for each time, print statement could be injected between each execution to analyze the time difference. For a multi statement procedure, Developers need to modify the stored procedure adding print statement, but it gives better control over the data to the developers. On the disadvantages side, one of the major points is its disability over getting time taken for compilation. Instead of PRINT statements, the time could be inserted into a table for further analysis is required. As datetime data type as it allows the value to be accurate up to 3 milliseconds the results may have vary with actual up to 3 milliseconds, and better than the previous method.

SQL Server has some other methods too.

SQL Server has a Set option which could be used to display the time taken: SET STATISTICS TIME ON

The setting could be reversed by using SET STATISTICS TIME OFF statement after the query. When this set option is ON. SQL Server will return message which may look similar to:

SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 164 ms.

Elapsed time is the execution time of the query.

However, there is another time involved in this query: The time taken to compile the query. To view that, the statement should be issued when SET STATISTICS TIME ON Statement is already executed. When a batch of statements is submitted, SQL Server goes through ALL of them but compiles one by one. As GO is considered as batch separator, inserting a GO statement between the SET command and the query will make SQL Server to consider each statement as a batch and compile and execute them separately. Executing the set command first and executing the query (i.e. in two batches) will also do the trick.

When done the messages may be different.

SQL Server parse and compile time:
CPU time = 62 ms, elapsed time = 72 ms.

(1139 row(s) affected)

SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 167 ms.

In this case, the time taken by the server for execution is 167 milliseconds. In addition to this, 72 milliseconds have been taken for compilation. The unit of measurement is in milliseconds and smaller figures will be rounded. When a multi statement stored procedure is called, the total time may slightly differ from the calculation. For this reason, SQL Server provides a summary too. Additionally, some of the internal operations like creating worktables, statistics on temporary tables etc. may create additional load but not be captured as individual statements. However, they will be added to the final cost.

1. IF OBJECT_ID('dbo.bsp_GetBigSales_ByTerritoryGroup') IS NOT NULL
2. DROP PROCEDURE dbo.bsp_GetBigSales_ByTerritoryGroup
3. GO
4.

5. CREATE PROCEDURE dbo.bsp_GetBigSales_ByTerritoryGroup
6.

7. @Group nvarchar(100)
8. AS
9. CREATE TABLE #Territory
10. (
11. TerritoryID int NOT NULL,
12. TerritoryName nvarchar(100)
13. )
14.

15. INSERT INTO #Territory (
16. TerritoryID,
17. TerritoryName)
18. SELECT T.TerritoryID,
19. T.[Name]
20. FROM Sales.SalesTerritory T
21. WHERE T.[Group] = @Group
22.

23. SELECT T.TerritoryName,
24. SH.ShipDate,
25. SUM(SD.LineTotal) AS ProductValue
26. FROM #Territory T
27. INNER JOIN Sales.Customer C
28. ON T.TerritoryID = C.TerritoryID
29. INNER JOIN Sales.SalesOrderHeader SH
30. ON SH.CustomerID = C.CustomerID
31. INNER JOIN Sales.SalesOrderDetail SD
32. ON SH.SalesOrderID = SD.SalesOrderID
33. GROUP BY T.TerritoryName,
34. SH.ShipDate
35. ORDER BY SH.ShipDate
36.

37. DROP TABLE #Territory
38. GO
39.

40.


41. SET STATISTICS TIME ON
42. GO
43. EXEC bsp_GetBigSales_ByTerritoryGroup
44. @Group = N'Europe'
45. SET STATISTICS TIME OFF
46. GO

The above stored procedure contains four SQL statements;

1. Creates a Temporary table (Lines 8 - 12)
2. Inserts Territory data into temporary table (Lines 14 - 20)
3. Selects the required data (Lines 22 - 34)
4. Drops the temporary table (Line 36)

When the stored procedure is executed it produces multiple lines of messages. The number of lines and values may vary based on several factors.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 8 ms.

(3 row(s) affected)

(1139 row(s) affected)

SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 549 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 11 ms.

SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 568 ms.

The first two sets of messages are compilation related. As we have four SQL statements, the next four statements refer the time taken for execution. The fifth line specifies the total time taken for the execution of the stored procedure. Users can note that the sum of time on each statement exceeds the value on the last line.

When a query with single statement or a few statements is used, this figure may be acceptable to use. When a stored procedure with multiple statements is executed, it will return multiple lines of information and it is difficult to get the values for each statement. This becomes extremely difficult if the stored procedure contains a while loop. Additionally, it is difficult to export the value into a table or spreadsheet for further calculation unless string manipulation is used.

Another method is to use Profiler. In profiler, the events that could be used for getting duration are

Stored Procedures

* RPC: Completed
* Sp: Completed
* Sp: Stmt Completed

TSQL

* SQL: Batch Completed
* SQL: Stmt Completed

The profiler could be set up to capture these events with these events by any user who has ALTER TRACE permission. Alternately, users could use a built-in template named Tuning too. Once set, Until it is stopped (or closed) it will capture the data. text data, start time and end time are additional columns which could be used. These two columns use SQL Server's date time data type and can hold data with 3/10 milliseconds accuracy. Users can include some more columns including CPU, Reads & Writes which is covered later in this article. To get maximum performance, the user should add filters on the application name, database ID (This ID could be obtained by executing the statement SELECT DB_ID() against the database or SELECT DB_ID('') against the server.), login name or host name (client computer which connects to the database server) could be used to get only the exact information needed. However, Profiler does not provide a mechanism to capture the time taken for compilation.

The duration in profiler is by default measured in milliseconds. However, SQL Server 2005 includes an option to measure the duration in microseconds. It is useful when examining statements which do not take much time but may being executed very frequently.

Running profiler from a client computer against a busy server is not recommended. It creates comparatively high volume of network traffic. Server side trace events could be used during the situation. It produces an output file which could be analyzed using profiler. Users can create the script for server side trace using Profiler from File -> Export menu

SQL Server 2005 provides feature rich dynamic management views and functions which displays the information on query execution time. sys.dm_exec_query_stats displays the information on elapsed time. In addition to elapsed time, this view displays time related to CLR code. The following code displays the details of cached executions for all code executed against AdventureWorks:

SELECT query.objectid
,SUBSTRING(text, stat.statement_start_offset/2,
(CASE WHEN stat.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),query.[text])) * 2
ELSE stat.statement_end_offset
END
- stat.statement_start_offset)/2) as query_text
,stat.execution_count
,stat.total_worker_time
,stat.last_worker_time
,stat.min_worker_time
,stat.max_worker_time
,stat.total_clr_time
,stat.last_clr_time
,stat.min_clr_time
,stat.max_clr_time
,stat.total_elapsed_time
,stat.last_elapsed_time
,stat.min_elapsed_time
,stat.max_elapsed_time
FROM sys.dm_exec_query_stats stat
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS query
WHERE [query].dbid = DB_ID('AdventureWorks')

Issues with Elapsed time

Elapsed time of a stored procedure may vary even in the same server at different times based on the number of operations in progress at that time, the resources it uses, number of pages cached and the parameters passed. Let's have a look on these reasons

*

A Change in resources available for query processing. This could happen when another process is running in parallel. It could be either some users running a resource intensive query, or SQL Server Agent may start certain jobs which may take some resources. Further, if the same computer could be used to run many services and a process running on a different service too could be the cause of the problem. However, developers can evaluate the stored when no other process is happening. Most of the jobs could be stopped temporarily. Creating such a situation in development environment is highly possible.
*

The plan would have been compiled during the first execution but reused during the second execution. Also, DBCC FREEPROCCACHE command could be used to remove the cached procedure information from memory. It will ensure the procedure is recompiled again and again and the same execution plan is not re-used. (The execution plan may be the same in terms of content, but the DBCC command ensures that it was recreated and not reused)
*

A check point process may occur in the middle of the executions which unload the data pages from memory, or some of the pages which were not available in the cache during the first execution and loaded into memory for the execution are used by the second execution. The check point process too could be controlled using command CHECKPOINT. DBCC DROPCLEANBUFFERS command could be issued to clean the buffers so that the data will always be read from the disk and not from memory.
*

Based on the values passed for the parameters, the number of data pages to be loaded may differ. They may cause even a recompile at certain statements within a stored procedure, and the execution plan may not be the same.

All the solutions provided above are good if the database is isolated for other users. But, if the developers are working against a centralized database server it may not be possible to run all tests during a time when no one else is working. In this case, running the same stored procedure for multiple times and analyzing the average time taken could be a good solution. Remember the application should issue the command multiple times for RPC: Completed event to capture the counters well.

CPU Cost

CPU cost will show how much of CPU cost is taken to execute the stored procedure. CPU is a valuable resource and taking more from CPU may make not only the database but the entire server system to be non-responsive. Adding a few cycles may not cost much high but, when many processes are doing that, the server may go into high CPU usage.

As seen before, SET STATISTICS TIME ON command displays the CPU time taken. Again, this option could not be used to get the information into a table or spread sheet application for further analysis. However, this refers the actual time taken for execution and not the estimated time calculated by the compiler

Again, Trace events (wither from server side or from client side using Profiler) could be used to capture the CPU usage. This is the actual usage during the execution and not the expected CPU time. The best events to capture CPU time are

Stored Procedures

* RPC: Completed
* Sp: Stmt Completed

TSQL

* SQL: Batch Completed
* SQL: Stmt Completed

SP: Completed event under stored procedure section cannot be used to capture CPU cost.

@@CPU_BUSY system global variable displays the time (in ticks) the CPU was busy since the last restart. It adds the time from all CPUs. When that number is multiplied by another global variable @@TIMETICKS it results the microseconds the CPU was busy. By selecting the values before and after the execution of the query, The CPU time taken by the query could be calculated. However, @@CPU_BUSY always return a whole number.

The set commands (SET SHOWPLAN_ALL ON, SET SHOWPLAN_XML ON and ET STATISTICS PROFILE ON) display the not the actual CPU cost, but the estimated cost along with other information. When the first two commands are issued, only the estimated execution plan is generated and the expected CPU cost (and expected IO cost) which the (actual) execution plan used will be displayed. Even though the last command does not prevent the code from being executed, it also displays the estimated cost from the plan used for execution.

The estimated costs do play a part when analyzing a query and optimizing it. However, it cannot be used to measure the performance of a query.

Windows Operating system comes with a tool called PerfMon.exe. The display name of the utility differs from operating system to operating system. In Windows XP and Windows 2003 it is named as "Performance". During the Windows 2000 days it had the name as "System Monitor". Even before, it was called as "Performance Monitor". This tool is mainly used to measure the activities happening at the server. Even though it has mechanism to monitor CPU usage Disk activity and Memory usage (and many more), it is not useful to monitor the resource usage as per a single query. Rather, it could be used to monitor the activities and the change when large column of transactions happen. This article excludes this tool from evaluation.

IO Cost

IO cost is one of the critical costs of the system. As discussed in the beginning, IO is one of the critical resources and unless IO cost is kept low, the performance of the stored procedure will go down. Cached pages (Cached page = 8kb of data loaded from hard disk into memory) may increase the performance by not reading the data from hard disk.

SET STATISTICS IO ON is one of the commands that could be used to identify the IO cost. Again, it has the same issue of, SET STATISTICS TIME ON as it prints the statement in the messages tab, and it is difficult to process. A typical output looks like this:

(2750 row(s) affected)
Table 'SalesOrderHeader'. Scan count 1, logical reads 1403, physical reads 1, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

1. The above lines stats quite a few information against this query which uses Sales.SalesOrderHeader:
2. A total of 1403 pages were read to complete the query (logical reads 1403).
3. Out of the above pages, only one page was read (physical reads 1) from disk and the rest were either already in memory or read into data cache by the read-ahead mechanism.
4. 1 page was read into the data cache by the read-ahead mechanism.
5. The pages were read from disk through a single pass scan.
6. The rest refers to large objects and if there is no large object (like nvarchar(MAX) or text) involved in the query, the figures will show zero values.

In this example, even though logical scan shows as 1403, it does not mean the query has processed 14903 distinct pages. If a particular page is scanned twice, the local scan will consider it as two. To get clear picture of actual pages scanned, DBCC DROPCLEANBUFFERS should be executed (to make the pages loaded before to be dropped) and the sum of physical and read-ahead reads to be calculated.

These statistics related commands can be combined with Print statements to identify the cost involved in each statement. These are really useful to identify the IO operations and the cost of them.

Using profiler (or server side trace events) the read and write (of pages) occurred could be captured. It will differentiate the pages read and pages written separately, but not the pages already in memory and read from disk. All the events mentioned for CPU cost could be used to capture reads and writes.

The dynamic management view sys.dm_exec_query_stats also could be used to view IO Cost. Simply adding the following columns in the query specified to measure elapsed time will give the details:

* total_physical_reads
* last_physical_reads
* min_physical_reads
* max_physical_reads
* total_logical_writes
* last_logical_writes
* min_logical_writes
* max_logical_writes
* total_logical_reads
* last_logical_reads
* min_logical_reads
* max_logical_reads

This view does not separate read-ahead reads and LOB related reads/writes. However, this could be used as calculated statistics against a query.

The details of this view will be available until the cache is cleared. If a stored procedure is not used for some time it will be removed from memory. Until such time this view could be used to gather data.

In the development environment, it is better to clear the cache (both data cache and proc cache) before starting with any tests.

Additionally, @@IO_BUSY also be used to measure the IO time. Like the CPU counterpart this system variable displays the number of ticks which the IO was busy. By using the same calculation method used to calculate CPU time, the microseconds of IO time also could be calculated.

What is important

The importance of these counters may differ from environment to environment and based on the nature and time of operation. For example during busy hours for some organizations it may be more important to serve as many requests as possible rather than responding faster. (A job could be done faster if all resources are used; but it may lead to system hanging for some time or no response to other requests.) Additionally, performance is also works with the mindset of the end users (users complain only when their expectations are not met!) and the developers need to understand and meet these expectations. When writing the stored procedures, developers need to analyze the critical area in the system they are working with, and choose the importance to the counters appropriately.

Conclusion

This article describes different counters used for measuring performance and ways of calculating them. This article also described on the issues with those methods and ways of overcoming them. These counters are useful for measuring how a stored procedure behaves and to optimize them.

source : http://sqlserveruniverse.com/content/PERF0600104282008MeasuringPerformanceOfStoredProcedures.aspx