Cognos Vs SSIS

We are trying to investigate the ETL tools available in the market and have narrowed down to two

COGNOS - ETL (Data Manager Tool)

Microsoft - SSIS

Is there a comparison study out there which would help us in this process. Also a list of things we need to look out for in a ETL tool will also help.

Thanks

http://www.180systems.com/BI-Comparison2006.xls

Looks like someone has done the research for you.

Is this what you are looking for?

cognos OLAP conversion to ms analysis service

Hello All,
I am new to this group. I am looking for a tool that will convert cognos
OLAp cubes into ms OLAP cubes. Anybody has an idea?
Thanks a lot
Angie..
Won't work using Replication - I'd repost in the Analysis Services/OLAP
groups....
Paul Ibison

Code Statistics

Hi !
I'm looking for a tool that can gather "statistics" of T-SQL code (SP,
triggers, UDF, Views)
By the word "statistics" I mean total number of code lines, number of
comment lines, number of constatant expression assignments (e.g. SET
@.A='Monday' or SET @.b=25)
Does anybody know such a tool or might advise some other solution to
get this information (but not too hard, like writing parcers)
Thank you in advance,
Alexey KudinovHi Alex
U can make use of sysobjects table or sp_help stored procedure.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Alex Kudinov" wrote:

> Community Message Not Available

Code Snippets

I am looking for a sample code to write a tool or a 3rd party tool that will enable me to get the same functionality as visual studio 2005 code snippets in SSMS.

We do not expose the necessary interfaces to have 3rd parties plug-in their components into SQL Server Management Studio in SQL Server 2005. We are looking at this for a future release. We do have a Template Explorer, which may be of use in the meantime.

Using SQL Server Management Studio Templates
How to: Use Templates in SQL Server Management Studio

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Maybe you are looking for something like this (with sourcecode)

http://www.codeproject.com/useritems/enisey.asp

|||

Paul I was able to use the templates, its just that I was hoping for similar style of code snippets that Visual Studio has, where I could right click and have some of common tsql scripts available while using a query window. But for now the templates look better than nothing. I am not sure where to put in a suggestions, but that would be my first to have code snippets like visual studio 2005 uses in ssms.

Jurgen thanks for sending the like, but I am looking for more custom snippet storage not neccessarily code generation tools. But Thank you for the suggestion.

Thanks Troutbum!

|||

We have a customer portal where you can file suggestions. It's called Microsoft Connect. You can submit your suggestion here: http://connect.microsoft.com/SQLServer/.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Code review tool for Stored procedures

Hi,
Is there any tool (freeware or licensed) which can automate code reviews for
stored procedures based on various parameters. I am looking out for somethin
g
similar to FXCop for stored procedures on SQL Server 2000.
Afaq ChoonawalaYou can start with SQL BPA
http://www.microsoft.com/downloads/...&displaylang=en
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Afaq" <Afaq@.discussions.microsoft.com> wrote in message
news:36129FBF-13FB-4492-B7F3-C21C75BD2B7A@.microsoft.com...
> Hi,
> Is there any tool (freeware or licensed) which can automate code reviews
> for
> stored procedures based on various parameters. I am looking out for
> something
> similar to FXCop for stored procedures on SQL Server 2000.
> --
> Afaq Choonawala

Code coverage tools

Do code coverage tools exist for SQL. I am not talking about really
complicated one. Just perhaps a tool that will scan a stored proc, then
mark/remove unused variables, for instance.
RegardsI'm not aware of any. It would be interesting, but you'd have to first
define what you mean by code coverage. To me, it is a testing algorithm
which determins what percentage of the possible code paths have been tested.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23HJp0HpJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Do code coverage tools exist for SQL. I am not talking about really
> complicated one. Just perhaps a tool that will scan a stored proc, then
> mark/remove unused variables, for instance.
> Regards|||Michael Hotek wrote:[vbcol=seagreen]
> I'm not aware of any. It would be interesting, but you'd have to first
> define what you mean by code coverage. To me, it is a testing algorithm
> which determins what percentage of the possible code paths have been tested.[/vbco
l]
I'd settle for dead code coverage. Anyway, sounds like a weekend project.|||It's something that is nice in theory, but again, it comes down to how you
define it. Finding unused variables is pretty straightforward. How do you
figure out whether a code path can be taken, no so trivial? How do you
figure out if a piece of code within a stored procedure is executed, again,
not so trivial. You'd need a much better definition of what you mean by
"code coverage" (a better term would be code validity) before trying to
create something.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:OUp9F%230JGHA.2248@.TK2MSFTNGP15.phx.gbl...
> Michael Hotek wrote:
> I'd settle for dead code coverage. Anyway, sounds like a weekend project.

Code coverage tools

Do code coverage tools exist for SQL. I am not talking about really
complicated one. Just perhaps a tool that will scan a stored proc, then
mark/remove unused variables, for instance.
RegardsI'm not aware of any. It would be interesting, but you'd have to first
define what you mean by code coverage. To me, it is a testing algorithm
which determins what percentage of the possible code paths have been tested.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23HJp0HpJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Do code coverage tools exist for SQL. I am not talking about really
> complicated one. Just perhaps a tool that will scan a stored proc, then
> mark/remove unused variables, for instance.
> Regards|||Michael Hotek wrote:
> I'm not aware of any. It would be interesting, but you'd have to first
> define what you mean by code coverage. To me, it is a testing algorithm
> which determins what percentage of the possible code paths have been tested.
I'd settle for dead code coverage. Anyway, sounds like a weekend project.|||It's something that is nice in theory, but again, it comes down to how you
define it. Finding unused variables is pretty straightforward. How do you
figure out whether a code path can be taken, no so trivial? How do you
figure out if a piece of code within a stored procedure is executed, again,
not so trivial. You'd need a much better definition of what you mean by
"code coverage" (a better term would be code validity) before trying to
create something.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:OUp9F%230JGHA.2248@.TK2MSFTNGP15.phx.gbl...
> Michael Hotek wrote:
>> I'm not aware of any. It would be interesting, but you'd have to first
>> define what you mean by code coverage. To me, it is a testing algorithm
>> which determins what percentage of the possible code paths have been
>> tested.
> I'd settle for dead code coverage. Anyway, sounds like a weekend project.

Code coverage tools

Do code coverage tools exist for SQL. I am not talking about really
complicated one. Just perhaps a tool that will scan a stored proc, then
mark/remove unused variables, for instance.
Regards
I'm not aware of any. It would be interesting, but you'd have to first
define what you mean by code coverage. To me, it is a testing algorithm
which determins what percentage of the possible code paths have been tested.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23HJp0HpJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Do code coverage tools exist for SQL. I am not talking about really
> complicated one. Just perhaps a tool that will scan a stored proc, then
> mark/remove unused variables, for instance.
> Regards
|||Michael Hotek wrote:
> I'm not aware of any. It would be interesting, but you'd have to first
> define what you mean by code coverage. To me, it is a testing algorithm
> which determins what percentage of the possible code paths have been tested.
I'd settle for dead code coverage. Anyway, sounds like a weekend project.
|||It's something that is nice in theory, but again, it comes down to how you
define it. Finding unused variables is pretty straightforward. How do you
figure out whether a code path can be taken, no so trivial? How do you
figure out if a piece of code within a stored procedure is executed, again,
not so trivial. You'd need a much better definition of what you mean by
"code coverage" (a better term would be code validity) before trying to
create something.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:OUp9F%230JGHA.2248@.TK2MSFTNGP15.phx.gbl...
> Michael Hotek wrote:
> I'd settle for dead code coverage. Anyway, sounds like a weekend project.

Code Coverage Tool

I want to analyze what statemetns were executed in stored procedures,
for example.
I'm trying hard to find tools for sql code coverage while there are
tons of such tools for Java or C#.
I was able to find only : http://www.sqlpower.com/dsa.html but I want
to compare it with other solutions.Andrei
Have you tried using SQL Server Profiler?
<andrei_tapt@.mail.ru> wrote in message
news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
>I want to analyze what statemetns were executed in stored procedures,
> for example.
> I'm trying hard to find tools for sql code coverage while there are
> tons of such tools for Java or C#.
> I was able to find only : http://www.sqlpower.com/dsa.html but I want
> to compare it with other solutions.
>|||Yes, I use it as performance analyzer. I know that I can save trace in
database but it doesn't provide me any info about what sql statements
were executed ... or I don't know how to get it.
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> Andrei
> Have you tried using SQL Server Profiler?
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> >I want to analyze what statemetns were executed in stored procedures,
> > for example.
> > I'm trying hard to find tools for sql code coverage while there are
> > tons of such tools for Java or C#.
> > I was able to find only : http://www.sqlpower.com/dsa.html but I want
> > to compare it with other solutions.
> >|||<andrei_tapt@.mail.ru> wrote in message
news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
?me any info about what sql statements
>were executed ... or I don't know how to get it.
Why> There are some events to provide it. Under TSQL events select
SQL:StmtCompleted
And see in the TEXT column the resuts
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
<andrei_tapt@.mail.ru> wrote in message
news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
Yes, I use it as performance analyzer. I know that I can save trace in
database but it doesn't provide me any info about what sql statements
were executed ... or I don't know how to get it.
Uri Dimant '?(?):
> Andrei
> Have you tried using SQL Server Profiler?
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> >I want to analyze what statemetns were executed in stored procedures,
> > for example.
> > I'm trying hard to find tools for sql code coverage while there are
> > tons of such tools for Java or C#.
> > I was able to find only : http://www.sqlpower.com/dsa.html but I want
> > to compare it with other solutions.
> >|||AFAIK, I will see full text of stored procedure in TEXT column, like
using sp_helptext with stored procedure name, but I want to know which
statements were executed in this stored procedure.
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> ?me any info about what sql statements
> >were executed ... or I don't know how to get it.
> Why> There are some events to provide it. Under TSQL events select
> SQL:StmtCompleted
> And see in the TEXT column the resuts
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
>
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> Yes, I use it as performance analyzer. I know that I can save trace in
> database but it doesn't provide me any info about what sql statements
> were executed ... or I don't know how to get it.
> Uri Dimant '?(?):
> > Andrei
> > Have you tried using SQL Server Profiler?
> >
> >
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> > >I want to analyze what statemetns were executed in stored procedures,
> > > for example.
> > > I'm trying hard to find tools for sql code coverage while there are
> > > tons of such tools for Java or C#.
> > > I was able to find only : http://www.sqlpower.com/dsa.html but I want
> > > to compare it with other solutions.
> > >|||Andrei, ty chital chto ya tebe napisal? Link etot chital?
<andrei_tapt@.mail.ru> wrote in message
news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
AFAIK, I will see full text of stored procedure in TEXT column, like
using sp_helptext with stored procedure name, but I want to know which
statements were executed in this stored procedure.
Uri Dimant '?(?):
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> ?me any info about what sql statements
> >were executed ... or I don't know how to get it.
> Why> There are some events to provide it. Under TSQL events select
> SQL:StmtCompleted
> And see in the TEXT column the resuts
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
>
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> Yes, I use it as performance analyzer. I know that I can save trace in
> database but it doesn't provide me any info about what sql statements
> were executed ... or I don't know how to get it.
> Uri Dimant '?(?):
> > Andrei
> > Have you tried using SQL Server Profiler?
> >
> >
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> > >I want to analyze what statemetns were executed in stored procedures,
> > > for example.
> > > I'm trying hard to find tools for sql code coverage while there are
> > > tons of such tools for Java or C#.
> > > I was able to find only : http://www.sqlpower.com/dsa.html but I want
> > > to compare it with other solutions.
> > >|||Spasibo, kak raz to chto nado:) Povozitsya pridetsya, no lucshe chem
nichego. Ran'she etu stat'u videl, no bystro probezhalsya b vnimania ne
obratil. Spasibo
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> Andrei, ty chital chto ya tebe napisal? Link etot chital?
> <andrei_tapt@.mail.ru> wrote in message
> news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
> AFAIK, I will see full text of stored procedure in TEXT column, like
> using sp_helptext with stored procedure name, but I want to know which
> statements were executed in this stored procedure.
> Uri Dimant '?(?):
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> > ?me any info about what sql statements
> > >were executed ... or I don't know how to get it.
> >
> > Why> There are some events to provide it. Under TSQL events select
> > SQL:StmtCompleted
> > And see in the TEXT column the resuts
> >
> > http://www.sql-server-performance.com/sql_server_profiler_tips.asp
> >
> >
> >
> >
> >
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> > Yes, I use it as performance analyzer. I know that I can save trace in
> > database but it doesn't provide me any info about what sql statements
> > were executed ... or I don't know how to get it.
> >
> > Uri Dimant '?(?):
> >
> > > Andrei
> > > Have you tried using SQL Server Profiler?
> > >
> > >
> > > <andrei_tapt@.mail.ru> wrote in message
> > > news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> > > >I want to analyze what statemetns were executed in stored procedures,
> > > > for example.
> > > > I'm trying hard to find tools for sql code coverage while there are
> > > > tons of such tools for Java or C#.
> > > > I was able to find only : http://www.sqlpower.com/dsa.html but I wa=nt
> > > > to compare it with other solutions.
> > > >|||Ok, ydachi, obrashaysya esli est' problemy
<andrei_tapt@.mail.ru> wrote in message
news:1155122187.034692.167400@.n13g2000cwa.googlegroups.com...
Spasibo, kak raz to chto nado:) Povozitsya pridetsya, no lucshe chem
nichego. Ran'she etu stat'u videl, no bystro probezhalsya b vnimania ne
obratil. Spasibo
Uri Dimant '?(?):
> Andrei, ty chital chto ya tebe napisal? Link etot chital?
> <andrei_tapt@.mail.ru> wrote in message
> news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
> AFAIK, I will see full text of stored procedure in TEXT column, like
> using sp_helptext with stored procedure name, but I want to know which
> statements were executed in this stored procedure.
> Uri Dimant '?(?):
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> > ?me any info about what sql statements
> > >were executed ... or I don't know how to get it.
> >
> > Why> There are some events to provide it. Under TSQL events select
> > SQL:StmtCompleted
> > And see in the TEXT column the resuts
> >
> > http://www.sql-server-performance.com/sql_server_profiler_tips.asp
> >
> >
> >
> >
> >
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> > Yes, I use it as performance analyzer. I know that I can save trace in
> > database but it doesn't provide me any info about what sql statements
> > were executed ... or I don't know how to get it.
> >
> > Uri Dimant '?(?):
> >
> > > Andrei
> > > Have you tried using SQL Server Profiler?
> > >
> > >
> > > <andrei_tapt@.mail.ru> wrote in message
> > > news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> > > >I want to analyze what statemetns were executed in stored procedures,
> > > > for example.
> > > > I'm trying hard to find tools for sql code coverage while there are
> > > > tons of such tools for Java or C#.
> > > > I was able to find only : http://www.sqlpower.com/dsa.html but I
> > > > want
> > > > to compare it with other solutions.
> > > >|||I did not know there were ANY code coverage tools for SQL Server. Now
I know there is at least one - thanks!
Roy
On 9 Aug 2006 00:43:24 -0700, andrei_tapt@.mail.ru wrote:
>I want to analyze what statemetns were executed in stored procedures,
>for example.
>I'm trying hard to find tools for sql code coverage while there are
>tons of such tools for Java or C#.
>I was able to find only : http://www.sqlpower.com/dsa.html but I want
>to compare it with other solutions.

Code Coverage Tool

I want to analyze what statemetns were executed in stored procedures,
for example.
I'm trying hard to find tools for sql code coverage while there are
tons of such tools for Java or C#.
I was able to find only : http://www.sqlpower.com/dsa.html but I want
to compare it with other solutions.Andrei
Have you tried using SQL Server Profiler?
<andrei_tapt@.mail.ru> wrote in message
news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
>I want to analyze what statemetns were executed in stored procedures,
> for example.
> I'm trying hard to find tools for sql code coverage while there are
> tons of such tools for Java or C#.
> I was able to find only : http://www.sqlpower.com/dsa.html but I want
> to compare it with other solutions.
>|||Yes, I use it as performance analyzer. I know that I can save trace in
database but it doesn't provide me any info about what sql statements
were executed ... or I don't know how to get it.
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
[vbcol=seagreen]
> Andrei
> Have you tried using SQL Server Profiler?
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...|||<andrei_tapt@.mail.ru> wrote in message
news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
?me any info about what sql statements
>were executed ... or I don't know how to get it.
Why> There are some events to provide it. Under TSQL events select
SQL:StmtCompleted
And see in the TEXT column the resuts
http://www.sql-server-performance.c...ofiler_tips.asp
<andrei_tapt@.mail.ru> wrote in message
news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
Yes, I use it as performance analyzer. I know that I can save trace in
database but it doesn't provide me any info about what sql statements
were executed ... or I don't know how to get it.
Uri Dimant '?(?):
[vbcol=seagreen]
> Andrei
> Have you tried using SQL Server Profiler?
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...|||AFAIK, I will see full text of stored procedure in TEXT column, like
using sp_helptext with stored procedure name, but I want to know which
statements were executed in this stored procedure.
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
[vbcol=seagreen]
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> ?me any info about what sql statements
> Why> There are some events to provide it. Under TSQL events select
> SQL:StmtCompleted
> And see in the TEXT column the resuts
> http://www.sql-server-performance.c...ofiler_tips.asp
>
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> Yes, I use it as performance analyzer. I know that I can save trace in
> database but it doesn't provide me any info about what sql statements
> were executed ... or I don't know how to get it.
> Uri Dimant '?(?):
>|||Andrei, ty chital chto ya tebe napisal? Link etot chital?
<andrei_tapt@.mail.ru> wrote in message
news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
AFAIK, I will see full text of stored procedure in TEXT column, like
using sp_helptext with stored procedure name, but I want to know which
statements were executed in this stored procedure.
Uri Dimant '?(?):
[vbcol=seagreen]
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> ?me any info about what sql statements
> Why> There are some events to provide it. Under TSQL events select
> SQL:StmtCompleted
> And see in the TEXT column the resuts
> http://www.sql-server-performance.c...ofiler_tips.asp
>
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> Yes, I use it as performance analyzer. I know that I can save trace in
> database but it doesn't provide me any info about what sql statements
> were executed ... or I don't know how to get it.
> Uri Dimant '?(?):
>|||Spasibo, kak raz to chto nado Povozitsya pridetsya, no lucshe chem
nichego. Ran'she etu stat'u videl, no bystro probezhalsya b vnimania ne
obratil. Spasibo
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
[vbcol=seagreen]
> Andrei, ty chital chto ya tebe napisal? Link etot chital?
> <andrei_tapt@.mail.ru> wrote in message
> news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
> AFAIK, I will see full text of stored procedure in TEXT column, like
> using sp_helptext with stored procedure name, but I want to know which
> statements were executed in this stored procedure.
> Uri Dimant '?(?):
>
nt[vbcol=seagreen]|||Ok, ydachi, obrashaysya esli est' problemy
<andrei_tapt@.mail.ru> wrote in message
news:1155122187.034692.167400@.n13g2000cwa.googlegroups.com...
Spasibo, kak raz to chto nado Povozitsya pridetsya, no lucshe chem
nichego. Ran'she etu stat'u videl, no bystro probezhalsya b vnimania ne
obratil. Spasibo
Uri Dimant '?(?):
[vbcol=seagreen]
> Andrei, ty chital chto ya tebe napisal? Link etot chital?
> <andrei_tapt@.mail.ru> wrote in message
> news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
> AFAIK, I will see full text of stored procedure in TEXT column, like
> using sp_helptext with stored procedure name, but I want to know which
> statements were executed in this stored procedure.
> Uri Dimant '?(?):
>|||I did not know there were ANY code coverage tools for SQL Server. Now
I know there is at least one - thanks!
Roy
On 9 Aug 2006 00:43:24 -0700, andrei_tapt@.mail.ru wrote:

>I want to analyze what statemetns were executed in stored procedures,
>for example.
>I'm trying hard to find tools for sql code coverage while there are
>tons of such tools for Java or C#.
>I was able to find only : http://www.sqlpower.com/dsa.html but I want
>to compare it with other solutions.

Coalesce increasing performance but Why?

This past wend I tried out a tool called Toad for Sql and it had a query
optimizer in it. I tossed in my slow performing query, walked away and 2
hours later had 50+ alternative queries.
One of the results had a 99.83% improvement. Logical Reads went from
62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
now took 35 seconds.
There were 5 left joins in the query. It simple replaced two of them (see
below), everything else stayed the same. im very happy with the results but
Id like to understand why COALESCE would make the query run almost 100%
faster.
LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
ac.boardID)
and
LEFT OUTER JOIN question_std_answer qsa
ON (qsa.cobrandID=ac.cobrandID
AND qsa.masterID=ac.masterID
AND qsa.accountID=ac.accountID
AND qsa.positionID=ac.positionID
AND qsa.jsrUserID=ac.jsrUserID
AND qsa.questionID=10)
with
LEFT OUTER JOIN question_std_answer qsa
ON qsa.cobrandID = ac.cobrandID
AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
AND qsa.accountID = ac.accountID
AND qsa.positionID = ac.positionID
AND qsa.jsrUserID = ac.jsrUserID
AND qsa.questionID = 10I'm not sure but I am guessing that the use of coalesce negated the indexes
that were previously being used, allowing different indexes to be used.
Have you tried recreating statistics and comparing the two queries again?
It sounds like SQL server is making a mistake in its chosen plan, but using
this function is removing an option and making it default to a muich better
plan.
"Brian" <brian@.nospam.com> wrote in message
news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> This past wend I tried out a tool called Toad for Sql and it had a
query
> optimizer in it. I tossed in my slow performing query, walked away and 2
> hours later had 50+ alternative queries.
> One of the results had a 99.83% improvement. Logical Reads went from
> 62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
> now took 35 seconds.
> There were 5 left joins in the query. It simple replaced two of them (see
> below), everything else stayed the same. im very happy with the results
but
> Id like to understand why COALESCE would make the query run almost 100%
> faster.
> LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
> LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
> ac.boardID)
> and
> LEFT OUTER JOIN question_std_answer qsa
> ON (qsa.cobrandID=ac.cobrandID
> AND qsa.masterID=ac.masterID
> AND qsa.accountID=ac.accountID
> AND qsa.positionID=ac.positionID
> AND qsa.jsrUserID=ac.jsrUserID
> AND qsa.questionID=10)
> with
> LEFT OUTER JOIN question_std_answer qsa
> ON qsa.cobrandID = ac.cobrandID
> AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
> AND qsa.accountID = ac.accountID
> AND qsa.positionID = ac.positionID
> AND qsa.jsrUserID = ac.jsrUserID
> AND qsa.questionID = 10
>|||I ran many times testing it against different date ranges always starting wi
th:
CHECKPOINT -- write dirty pages from data cache to disk
DBCC DROPCLEANBUFFERS -- clear the data cache
DBCC FREEPROCCACHE -- clear the procedure cache
Everytime is performed essentially the same give or take a second or two.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message news:etU6gJ7OGHA.916@.T
K2MSFTNGP10.phx.gbl...
> I'm not sure but I am guessing that the use of coalesce negated the indexe
s
> that were previously being used, allowing different indexes to be used.
> Have you tried recreating statistics and comparing the two queries again?
> It sounds like SQL server is making a mistake in its chosen plan, but usin
g
> this function is removing an option and making it default to a muich bette
r
> plan.
>
> "Brian" <brian@.nospam.com> wrote in message
> news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> query
> but
>
>|||What does the Execution Plan tell you?
http://msdn.microsoft.com/library/d... />
1_5pde.asp
"Brian" <brian@.nospam.com> wrote in message
news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> This past wend I tried out a tool called Toad for Sql and it had a
> query optimizer in it. I tossed in my slow performing query, walked away
> and 2 hours later had 50+ alternative queries.
> One of the results had a 99.83% improvement. Logical Reads went from
> 62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
> now took 35 seconds.
> There were 5 left joins in the query. It simple replaced two of them (see
> below), everything else stayed the same. im very happy with the results
> but Id like to understand why COALESCE would make the query run almost
> 100% faster.
> LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
> LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
> ac.boardID)
> and
> LEFT OUTER JOIN question_std_answer qsa
> ON (qsa.cobrandID=ac.cobrandID
> AND qsa.masterID=ac.masterID
> AND qsa.accountID=ac.accountID
> AND qsa.positionID=ac.positionID
> AND qsa.jsrUserID=ac.jsrUserID
> AND qsa.questionID=10)
> with
> LEFT OUTER JOIN question_std_answer qsa
> ON qsa.cobrandID = ac.cobrandID
> AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
> AND qsa.accountID = ac.accountID
> AND qsa.positionID = ac.positionID
> AND qsa.jsrUserID = ac.jsrUserID
> AND qsa.questionID = 10
>|||Thanks for replying JT ... Im by no means a DBA .. what should I focus on
when looking at the Execution Plan?
Thanks!
"JT" <someone@.microsoft.com> wrote in message
news:%23eMsok8OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> What does the Execution Plan tell you?
> http://msdn.microsoft.com/library/d...>
n_1_5pde.asp
> "Brian" <brian@.nospam.com> wrote in message
> news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||SQL Server does not execute SQL. It interprets SQL, compiles an execution
plan, and then uses that plan to perform a sequence of physical operations
such as index scans, joins, spooling, etc. You can deterministically compare
the design merits of one query versus another similar query by studying
their execution plan. For example, does one query perform a full table scan
while an alternative query perform a more efficient index scan?
Below are a few articles describing in more detail how to gain useful
information from execution plans:
SQL Tuning Tutorial - Understanding a Database Execution Plan (1)
http://www.codeproject.com/cs/datab...-tutorial-1.asp
How to Select Indexes for Your SQL Server Tables
http://www.sql-server-performance.com/mr_indexing.asp
SQL Server Query Execution Plan Analysis
http://www.sql-server-performance.c...an_analysis.asp
"Brian" <brian@.nospam.com> wrote in message
news:eZz$s18OGHA.3064@.TK2MSFTNGP10.phx.gbl...
> Thanks for replying JT ... Im by no means a DBA .. what should I focus on
> when looking at the Execution Plan?
> Thanks!
> "JT" <someone@.microsoft.com> wrote in message
> news:%23eMsok8OGHA.2124@.TK2MSFTNGP14.phx.gbl...
>|||I would guess that this is based on the use of
coalesce to force the use of an index.This is
simply a *trick* that was put forward by
'Umachandar Jayachandran' then an MVP.The idea is
that an index can be forced for any data type
by using the datatype boundries.Although a boundry
is not being used here the construct is probably
enough to force the use of an index.
I don't think this trick is talked about in a kb
for obvious reasons:)
I refer the interested reader to these threads:
http://tinyurl.com/rpa2x
http://tinyurl.com/n2oql
http://tinyurl.com/ns9aq
http://tinyurl.com/n5v4y
$.02 from
www.rac4sql.net|||No, this is a different trick. This trick will have the result that the
optimizer will not consider certain (underperforming) access paths. The
trick that Umachandar introduced is not used for joins, but for
filtering expressions (i.e. the WHERE clause).
Gert-Jan
05ponyGT wrote:
> I would guess that this is based on the use of
> coalesce to force the use of an index.This is
> simply a *trick* that was put forward by
> 'Umachandar Jayachandran' then an MVP.The idea is
> that an index can be forced for any data type
> by using the datatype boundries.Although a boundry
> is not being used here the construct is probably
> enough to force the use of an index.
> I don't think this trick is talked about in a kb
> for obvious reasons:)
> I refer the interested reader to these threads:
> http://tinyurl.com/rpa2x
> http://tinyurl.com/n2oql
> http://tinyurl.com/ns9aq
> http://tinyurl.com/n5v4y
> $.02 from
> www.rac4sql.net|||This comes as a shock as I'm so rarely wrong:(
Wonder why we use the term *trick* and not *kludge* :)
Anyway this is just another illustration of turning *what* not *how*
on its head.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:440499B7.72531652@.toomuchspamalready.nl...
> No, this is a different trick. This trick will have the result that the
> optimizer will not consider certain (underperforming) access paths. The
> trick that Umachandar introduced is not used for joins, but for
> filtering expressions (i.e. the WHERE clause).
> Gert-Jan
>
> 05ponyGT wrote: