Named Set with TOPCOUNT Not Working Correctly

I need to create reports that contain our top 15 customers at any given point in time. I created the following Named Set using the TopCount function in SSAS 2005:

TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

When I bring this set into the client (Excel 2007 pivot table), its shows our top 15 customers for all time. When I add a date filter for just 2007, it doesn't update the list to just show the top customers for 2007. It just keeps the same "all time" group.

When I create this named set in a browser query, it works correctly. That is, it shows the top 15 customers for 2007. Is there any way I can get the Named Set created in SSAS to show the correct data based on the filtered date in the client?

Here is the browser query:

WITHSET

[Top Performers]

AS

'TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])'

SELECT

{[Measures].[Net Ship Dollars]} ONCOLUMNS,

{[Top Performers]}ONROWS

FROM Shipments

Where ([Date].[Calendar Year].&[2007])

Thank you.

David

Are you using AS 2005 SP2? This problem has been fixed there.|||

The problem here is because the named set in the MDX Script gets resolved when it is first parsed at which time all the other dimensions will be set to their default members (which is usually the "All" member). Using the EXISTING statement in your set definition should force it to be re-evaluated with the current context each time it is used.

eg

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

|||I am using SP2 but still experiencing the problem.|||

Darren,

I tried this but the same problem is occuring. Apparently, the default member of the time hierarchy determines what customer members to return. When I changed the default member to 2007, the named set showed the top customers for 2007. I came across another post which confirmed this as a problem. Does anybody have any workarounds? Thyank you.

David

|||

Hi David,

It seems that I did not read your full question and missed some information. So, just want to clarify. Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement. Daren is right in the fact that named sets created in the cube scope or session scope (using Create statement) are evaluated when the CREATE statement is executed, therefore TopCount is calculated in the context of the default member. Sets created in the query scope using With clause are evaluated right after the WHERE clause and therefore take current member defined by the where clause in concideration (this is why your query with WITH statement works as you expect and query with named set from CREATE statement return different results). There are it's own pros on having static named sets (mostly performance), but in your case you should try to avoid using TopCount in the named set, if you want the members on other axis to affect results of the topcount.

Irina

P.S.

>>> I came across another post which confirmed this as a problem.

Could you please point me to this thread?

|||

Irina Gorbach wrote:

Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement.

Of course! sorry for leading you slightly astray. The EXISTING statement will work if you have a set in a calculation

eg

CREATE MEMBER CurrentCube.Measures.CustomersInTop10Pcnt AS COUNT(TOPPERCENT(EXISTING Customer.Customer.Members, 150 Measures.Sales)

This would re-evaluate the set used in the toppercent function based on the current slicing conditions, which is similar, but different.

|||so is this the solution then?

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

or is there another way?

Also, how do you get it to sort correctly in excel, if you pull over the named set it just shows you the top X sorted by the SET Expression, whereas if you do an MDX query it sorts it by the Numeric Expression

Named Set with TOPCOUNT Not Working Correctly

I need to create reports that contain our top 15 customers at any given point in time. I created the following Named Set using the TopCount function in SSAS 2005:

TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

When I bring this set into the client (Excel 2007 pivot table), its shows our top 15 customers for all time. When I add a date filter for just 2007, it doesn't update the list to just show the top customers for 2007. It just keeps the same "all time" group.

When I create this named set in a browser query, it works correctly. That is, it shows the top 15 customers for 2007. Is there any way I can get the Named Set created in SSAS to show the correct data based on the filtered date in the client?

Here is the browser query:

WITH SET

[Top Performers]

AS

'TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])'

SELECT

{[Measures].[Net Ship Dollars]} ON COLUMNS,

{[Top Performers]}ON ROWS

FROM Shipments

Where ([Date].[Calendar Year].&[2007])

Thank you.

David

Are you using AS 2005 SP2? This problem has been fixed there.|||

The problem here is because the named set in the MDX Script gets resolved when it is first parsed at which time all the other dimensions will be set to their default members (which is usually the "All" member). Using the EXISTING statement in your set definition should force it to be re-evaluated with the current context each time it is used.

eg

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

|||I am using SP2 but still experiencing the problem.|||

Darren,

I tried this but the same problem is occuring. Apparently, the default member of the time hierarchy determines what customer members to return. When I changed the default member to 2007, the named set showed the top customers for 2007. I came across another post which confirmed this as a problem. Does anybody have any workarounds? Thyank you.

David

|||

Hi David,

It seems that I did not read your full question and missed some information. So, just want to clarify. Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement. Daren is right in the fact that named sets created in the cube scope or session scope (using Create statement) are evaluated when the CREATE statement is executed, therefore TopCount is calculated in the context of the default member. Sets created in the query scope using With clause are evaluated right after the WHERE clause and therefore take current member defined by the where clause in concideration (this is why your query with WITH statement works as you expect and query with named set from CREATE statement return different results). There are it's own pros on having static named sets (mostly performance), but in your case you should try to avoid using TopCount in the named set, if you want the members on other axis to affect results of the topcount.

Irina

P.S.

>>> I came across another post which confirmed this as a problem.

Could you please point me to this thread?

|||

Irina Gorbach wrote:

Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement.

Of course! sorry for leading you slightly astray. The EXISTING statement will work if you have a set in a calculation

eg

CREATE MEMBER CurrentCube.Measures.CustomersInTop10Pcnt AS COUNT(TOPPERCENT(EXISTING Customer.Customer.Members, 150 Measures.Sales)

This would re-evaluate the set used in the toppercent function based on the current slicing conditions, which is similar, but different.

|||so is this the solution then?

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

or is there another way?

Also, how do you get it to sort correctly in excel, if you pull over the named set it just shows you the top X sorted by the SET Expression, whereas if you do an MDX query it sorts it by the Numeric Expression
sql

Named Set Question

I want to create a named set that includes a list of some of our major customers. As part of the set, I also want to add a calculated member that represents a subtotal of customers. For example:

{[Customer].[Customer Hierarchy].[Store].&[Smith Stores],[Customer].[Customer Hierarchy].[Store].&[Williams Stores],[Customer].[Customer Hierarchy].[Store].&[Lion Stores]}

represents a named set of three customers. In the named set, I want to add Smith Stores and Williams Stores together to create a subtotal called Midwest Group. So, my revised set would look like:

{[Customer].[Customer Hierarchy].[Store].&[Smith Stores],[Customer].[Customer Hierarchy].[Store].&[Williams Stores],[Customer].[Customer Hierarchy].[Store].&[Lion Stores], [Customer].[Customer Hierarchy].[Store].&[Midwest Group]}.

First, can I create a calculation such as [Customer].[Customer Hierarchy].[Store].&[Smith Stores] + [Customer].[Customer Hierarchy].[Store].&[Williams Stores] AS 'Midwest Group' and add it directly to the named set. Or can I create this as a calculated member and add this member to the named set.

Is what I would like to do possible with named sets?

Thank you.

David

You will have to first create calculated member and then add it to the set. Alternative approach is to add new attribute to the Customer dimension with members Major/Minor. This way you will be able to get your Major customer by slicing on that attribute. You also will be able to do any grouping you want. This is more scalable approach once your company grows and you have more than 3 major customers |||

Mosha, when adding the calculated member to the named set, are there any restrictions. For example, does the calculated member need to be in the same hierarchy as the other members of the set. Also, are there any syntax differences when adding the calculated member. For example, is it treated like any other tuple and I would just separate it from the other members with a comma? Thank you.

David

|||There are no special restrictions. The calculated member will have to be from the same hierarchy as other members, but this is not special requirements for the calculated members, all tuples in the set must have same dimensionality (hierarchility). Syntax is the same - if you enumerate the members, than use comma as separator.|||

Mosha, Thank you for the answer. One problem remains. I get the named set working correctly when I run an MDX query in Management Studio. I have the named set appearing in the row axis and everything displays correctly. When I use the same named set in an Excel 2007 pivot table, I get an error message -- "A set has been encountered that cannot contain calculated members". I do have "show calculated members from OLAP Server" box checked in Pivot Table Options in Excel. Is this a bug with Excel 2007?

David

|||I am not sure what exactly you do in Excel - but it is possible that it is limitation of either Excel or AS (the error message is actually from AS, not from Excel).|||

Mosha,

Is it unusual that an MDX query would work correctly in SQL Server Management Studio but yield an error in a front end client?

David

|||Well, obviously the query that you wrote in Management Studio and the query that Excel generated are different queries.

Named Pipes vs TCP/IP

Why would one choose Named Pipes over TCP/IP for the client net lib? We have
noticed for some of our customers that changing to named pipes speeds up the
response times of our application. But it is my understanding that a
properly configured LAN should work fine with TCP/IP.
Thanks,
Bob Castleman
SuccessWare Software
It is my understanding that named pipes have slightly less overhead than
TCP/IP but there it is usually not enough to warrant using named pipes. TCP
is much more versatile and should be encouraged in general over named pipes.
One thing to note is that WIN2003 has a 25% higher throuput in TCP over
Win2000 and DNS lookups are up to 120% faster as well. If your clients are
looking for speed improvements and are not on Win2003 they should try it
out. Another thing to note is that usually when clients tell me one
protocol is faster than another I tend to find there is too much chatter
from the client to the server in the first place. The more packets sent
back and forth the more likely you will have performance issues under heavy
load.
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> Why would one choose Named Pipes over TCP/IP for the client net lib? We
have
> noticed for some of our customers that changing to named pipes speeds up
the
> response times of our application. But it is my understanding that a
> properly configured LAN should work fine with TCP/IP.
> Thanks,
> Bob Castleman
> SuccessWare Software
>
|||A specific example of what we are running into happened this morning. A
customer called complaining of speed problems. Everything was "fine" on
Friday and today it was slow. CPU load on the database server was around 2%.
Disks weren't thrasing, etc. I had him change a workstation from TCP/IP to
Named Pipes and the time it took to load our application went from 20
seconds to 2 seconds. He reported a similar effect on the other
workstations. Nothing I have been able to find about TCP/IP and Named Pipes
would explain this behavior. This seems like some problem in network
communication, especially since the server was basically sitting idle.
I personally don't like "quick fixes". We run into this with just enough
regularity that it would be good to know what's really happening so we can
address the underlying problem.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
> It is my understanding that named pipes have slightly less overhead than
> TCP/IP but there it is usually not enough to warrant using named pipes.
> TCP
> is much more versatile and should be encouraged in general over named
> pipes.
> One thing to note is that WIN2003 has a 25% higher throuput in TCP over
> Win2000 and DNS lookups are up to 120% faster as well. If your clients
> are
> looking for speed improvements and are not on Win2003 they should try it
> out. Another thing to note is that usually when clients tell me one
> protocol is faster than another I tend to find there is too much chatter
> from the client to the server in the first place. The more packets sent
> back and forth the more likely you will have performance issues under
> heavy
> load.
> --
> Andrew J. Kelly SQL MVP
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> have
> the
>
|||Simply some facts top start with: Named Pipes is a OSI level 7 (Application)
protocol, WinSock is OSI level 5 (Session), therefore in a TCP only based
network, Named Pipes actually goes over WinSock, so it does not make any
sense that TCP Winsock is slower in that case, which is also not the
experience, and this is why we use TCP to perform client server benchmarks
for SQL Server. When running on the same machine, Named Pipes is faster then
TCP, because it becomes a Local Pipe call (this is where the magic . (dot)
notation is coming from).
Also since Named Pipes are file system objects at kernel level the
scalability compared to WinSock is much less, which is why if you need to
handle large number of connections you always have to use TCP sockets
instead of named pipes. Which each OS release thresholds move, like Andrew
said in Windows Server 2003, TCP sockets and DNS performance increase
dramatically. For example in NT 4.0 you would run out of file handles when
you would have more then 100-150 named pipes connections, this is not longer
a problem with Windows 2000 and Windows Server 2004, but this is to indicate
that each protocol have there own characteristics and scalability
thresholds.
In general the default choice is and should be TCP sockets for all remote
connections, if you are running on the same server like batch process you
might consider named pipes using a local pipe (using the . (dot) notation).
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
> It is my understanding that named pipes have slightly less overhead than
> TCP/IP but there it is usually not enough to warrant using named pipes.
> TCP
> is much more versatile and should be encouraged in general over named
> pipes.
> One thing to note is that WIN2003 has a 25% higher throuput in TCP over
> Win2000 and DNS lookups are up to 120% faster as well. If your clients
> are
> looking for speed improvements and are not on Win2003 they should try it
> out. Another thing to note is that usually when clients tell me one
> protocol is faster than another I tend to find there is too much chatter
> from the client to the server in the first place. The more packets sent
> back and forth the more likely you will have performance issues under
> heavy
> load.
> --
> Andrew J. Kelly SQL MVP
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> have
> the
>
|||Anti.virus program trying to AV check the named pipes stuff?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Castleman" <nomail@.here> wrote in message news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
>A specific example of what we are running into happened this morning. A customer called complaining
>of speed problems. Everything was "fine" on Friday and today it was slow. CPU load on the database
>server was around 2%. Disks weren't thrasing, etc. I had him change a workstation from TCP/IP to
>Named Pipes and the time it took to load our application went from 20 seconds to 2 seconds. He
>reported a similar effect on the other workstations. Nothing I have been able to find about TCP/IP
>and Named Pipes would explain this behavior. This seems like some problem in network communication,
>especially since the server was basically sitting idle.
> I personally don't like "quick fixes". We run into this with just enough regularity that it would
> be good to know what's really happening so we can address the underlying problem.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
>
|||An other option we see often are a DNS name resolution problems, use tracert
to determine the route taken.
An other thought are these notebooks, that connect wireless as well, or at
home? In which case it most of the times helps to dump the DNS resolver
cache of the workstation. See IPCONFIG /flushdns
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e6n8opOlEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Anti.virus program trying to AV check the named pipes stuff?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
>
|||"Bob Castleman" <nomail@.here> wrote in message
news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> Why would one choose Named Pipes over TCP/IP for the client net lib? We
have
> noticed for some of our customers that changing to named pipes speeds up
the
> response times of our application. But it is my understanding that a
> properly configured LAN should work fine with TCP/IP.
Don't know if this is your problem, but there was a bug a while ago in SQL
Server 2000 to do with the introduction of Kerberos aware authentication
using DBLibrary connections over TCP/IP.
IIRC, the SQL Client and SQL Server would not correctly negotiate packet
sizes. This could be circumvented by explicitly overriding the default
packet size in the client app. This was resolved in SQL Server SP2.
Kind Regards, Howard
|||Yes I have seen where issues with DNS have caused situations such as this as
well.
Andrew J. Kelly SQL MVP
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:e4O$dsOlEHA.3712@.TK2MSFTNGP15.phx.gbl...
> An other option we see often are a DNS name resolution problems, use
tracert
> to determine the route taken.
> An other thought are these notebooks, that connect wireless as well, or at
> home? In which case it most of the times helps to dump the DNS resolver
> cache of the workstation. See IPCONFIG /flushdns
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2004 All rights reserved.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:e6n8opOlEHA.3372@.TK2MSFTNGP09.phx.gbl...
network[vbcol=seagreen]
enough[vbcol=seagreen]
than[vbcol=seagreen]
pipes.[vbcol=seagreen]
over[vbcol=seagreen]
clients[vbcol=seagreen]
it[vbcol=seagreen]
chatter[vbcol=seagreen]
sent[vbcol=seagreen]
We
>
|||Same here.
Basically if the DNS server is unavailable the client will wait for 20-30
seconds, and then resolve the servername in a different way.
You can do a quick check if this is the issue by connecting to the server
via both the IP-address and the servername. IP-address should behave
normally and the servername will have the 30 second delay.
Jacco Schalkwijk
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uD8FsIPlEHA.3356@.TK2MSFTNGP14.phx.gbl...
> Yes I have seen where issues with DNS have caused situations such as this
> as
> well.
> --
> Andrew J. Kelly SQL MVP
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:e4O$dsOlEHA.3712@.TK2MSFTNGP15.phx.gbl...
> tracert
> rights.
> in
> network
> enough
> than
> pipes.
> over
> clients
> it
> chatter
> sent
> We
>
|||Thanks,
I'll try that.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uXQM9FQlEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Same here.
> Basically if the DNS server is unavailable the client will wait for 20-30
> seconds, and then resolve the servername in a different way.
> You can do a quick check if this is the issue by connecting to the server
> via both the IP-address and the servername. IP-address should behave
> normally and the servername will have the 30 second delay.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uD8FsIPlEHA.3356@.TK2MSFTNGP14.phx.gbl...
>

Named Pipes vs TCP/IP

Why would one choose Named Pipes over TCP/IP for the client net lib? We have
noticed for some of our customers that changing to named pipes speeds up the
response times of our application. But it is my understanding that a
properly configured LAN should work fine with TCP/IP.
Thanks,
Bob Castleman
SuccessWare SoftwareIt is my understanding that named pipes have slightly less overhead than
TCP/IP but there it is usually not enough to warrant using named pipes. TCP
is much more versatile and should be encouraged in general over named pipes.
One thing to note is that WIN2003 has a 25% higher throuput in TCP over
Win2000 and DNS lookups are up to 120% faster as well. If your clients are
looking for speed improvements and are not on Win2003 they should try it
out. Another thing to note is that usually when clients tell me one
protocol is faster than another I tend to find there is too much chatter
from the client to the server in the first place. The more packets sent
back and forth the more likely you will have performance issues under heavy
load.
--
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> Why would one choose Named Pipes over TCP/IP for the client net lib? We
have
> noticed for some of our customers that changing to named pipes speeds up
the
> response times of our application. But it is my understanding that a
> properly configured LAN should work fine with TCP/IP.
> Thanks,
> Bob Castleman
> SuccessWare Software
>|||http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/optimsql/odp_tun_1a_7fcj.asp
Peter
"Status quo, you know, that is Latin for "the mess we're
in."
Ronald Reagan
>--Original Message--
>Why would one choose Named Pipes over TCP/IP for the
client net lib? We have
>noticed for some of our customers that changing to named
pipes speeds up the
>response times of our application. But it is my
understanding that a
>properly configured LAN should work fine with TCP/IP.
>Thanks,
>Bob Castleman
>SuccessWare Software
>
>.
>|||A specific example of what we are running into happened this morning. A
customer called complaining of speed problems. Everything was "fine" on
Friday and today it was slow. CPU load on the database server was around 2%.
Disks weren't thrasing, etc. I had him change a workstation from TCP/IP to
Named Pipes and the time it took to load our application went from 20
seconds to 2 seconds. He reported a similar effect on the other
workstations. Nothing I have been able to find about TCP/IP and Named Pipes
would explain this behavior. This seems like some problem in network
communication, especially since the server was basically sitting idle.
I personally don't like "quick fixes". We run into this with just enough
regularity that it would be good to know what's really happening so we can
address the underlying problem.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
> It is my understanding that named pipes have slightly less overhead than
> TCP/IP but there it is usually not enough to warrant using named pipes.
> TCP
> is much more versatile and should be encouraged in general over named
> pipes.
> One thing to note is that WIN2003 has a 25% higher throuput in TCP over
> Win2000 and DNS lookups are up to 120% faster as well. If your clients
> are
> looking for speed improvements and are not on Win2003 they should try it
> out. Another thing to note is that usually when clients tell me one
> protocol is faster than another I tend to find there is too much chatter
> from the client to the server in the first place. The more packets sent
> back and forth the more likely you will have performance issues under
> heavy
> load.
> --
> Andrew J. Kelly SQL MVP
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
>> Why would one choose Named Pipes over TCP/IP for the client net lib? We
> have
>> noticed for some of our customers that changing to named pipes speeds up
> the
>> response times of our application. But it is my understanding that a
>> properly configured LAN should work fine with TCP/IP.
>> Thanks,
>> Bob Castleman
>> SuccessWare Software
>>
>|||Simply some facts top start with: Named Pipes is a OSI level 7 (Application)
protocol, WinSock is OSI level 5 (Session), therefore in a TCP only based
network, Named Pipes actually goes over WinSock, so it does not make any
sense that TCP Winsock is slower in that case, which is also not the
experience, and this is why we use TCP to perform client server benchmarks
for SQL Server. When running on the same machine, Named Pipes is faster then
TCP, because it becomes a Local Pipe call (this is where the magic . (dot)
notation is coming from).
Also since Named Pipes are file system objects at kernel level the
scalability compared to WinSock is much less, which is why if you need to
handle large number of connections you always have to use TCP sockets
instead of named pipes. Which each OS release thresholds move, like Andrew
said in Windows Server 2003, TCP sockets and DNS performance increase
dramatically. For example in NT 4.0 you would run out of file handles when
you would have more then 100-150 named pipes connections, this is not longer
a problem with Windows 2000 and Windows Server 2004, but this is to indicate
that each protocol have there own characteristics and scalability
thresholds.
In general the default choice is and should be TCP sockets for all remote
connections, if you are running on the same server like batch process you
might consider named pipes using a local pipe (using the . (dot) notation).
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
> It is my understanding that named pipes have slightly less overhead than
> TCP/IP but there it is usually not enough to warrant using named pipes.
> TCP
> is much more versatile and should be encouraged in general over named
> pipes.
> One thing to note is that WIN2003 has a 25% higher throuput in TCP over
> Win2000 and DNS lookups are up to 120% faster as well. If your clients
> are
> looking for speed improvements and are not on Win2003 they should try it
> out. Another thing to note is that usually when clients tell me one
> protocol is faster than another I tend to find there is too much chatter
> from the client to the server in the first place. The more packets sent
> back and forth the more likely you will have performance issues under
> heavy
> load.
> --
> Andrew J. Kelly SQL MVP
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
>> Why would one choose Named Pipes over TCP/IP for the client net lib? We
> have
>> noticed for some of our customers that changing to named pipes speeds up
> the
>> response times of our application. But it is my understanding that a
>> properly configured LAN should work fine with TCP/IP.
>> Thanks,
>> Bob Castleman
>> SuccessWare Software
>>
>|||Anti.virus program trying to AV check the named pipes stuff?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Castleman" <nomail@.here> wrote in message news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
>A specific example of what we are running into happened this morning. A customer called complaining
>of speed problems. Everything was "fine" on Friday and today it was slow. CPU load on the database
>server was around 2%. Disks weren't thrasing, etc. I had him change a workstation from TCP/IP to
>Named Pipes and the time it took to load our application went from 20 seconds to 2 seconds. He
>reported a similar effect on the other workstations. Nothing I have been able to find about TCP/IP
>and Named Pipes would explain this behavior. This seems like some problem in network communication,
>especially since the server was basically sitting idle.
> I personally don't like "quick fixes". We run into this with just enough regularity that it would
> be good to know what's really happening so we can address the underlying problem.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
>> It is my understanding that named pipes have slightly less overhead than
>> TCP/IP but there it is usually not enough to warrant using named pipes. TCP
>> is much more versatile and should be encouraged in general over named pipes.
>> One thing to note is that WIN2003 has a 25% higher throuput in TCP over
>> Win2000 and DNS lookups are up to 120% faster as well. If your clients are
>> looking for speed improvements and are not on Win2003 they should try it
>> out. Another thing to note is that usually when clients tell me one
>> protocol is faster than another I tend to find there is too much chatter
>> from the client to the server in the first place. The more packets sent
>> back and forth the more likely you will have performance issues under heavy
>> load.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Bob Castleman" <nomail@.here> wrote in message
>> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
>> Why would one choose Named Pipes over TCP/IP for the client net lib? We
>> have
>> noticed for some of our customers that changing to named pipes speeds up
>> the
>> response times of our application. But it is my understanding that a
>> properly configured LAN should work fine with TCP/IP.
>> Thanks,
>> Bob Castleman
>> SuccessWare Software
>>
>>
>|||An other option we see often are a DNS name resolution problems, use tracert
to determine the route taken.
An other thought are these notebooks, that connect wireless as well, or at
home? In which case it most of the times helps to dump the DNS resolver
cache of the workstation. See IPCONFIG /flushdns
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e6n8opOlEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Anti.virus program trying to AV check the named pipes stuff?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
>>A specific example of what we are running into happened this morning. A
>>customer called complaining of speed problems. Everything was "fine" on
>>Friday and today it was slow. CPU load on the database server was around
>>2%. Disks weren't thrasing, etc. I had him change a workstation from
>>TCP/IP to Named Pipes and the time it took to load our application went
>>from 20 seconds to 2 seconds. He reported a similar effect on the other
>>workstations. Nothing I have been able to find about TCP/IP and Named
>>Pipes would explain this behavior. This seems like some problem in network
>>communication, especially since the server was basically sitting idle.
>> I personally don't like "quick fixes". We run into this with just enough
>> regularity that it would be good to know what's really happening so we
>> can address the underlying problem.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
>> It is my understanding that named pipes have slightly less overhead than
>> TCP/IP but there it is usually not enough to warrant using named pipes.
>> TCP
>> is much more versatile and should be encouraged in general over named
>> pipes.
>> One thing to note is that WIN2003 has a 25% higher throuput in TCP over
>> Win2000 and DNS lookups are up to 120% faster as well. If your clients
>> are
>> looking for speed improvements and are not on Win2003 they should try it
>> out. Another thing to note is that usually when clients tell me one
>> protocol is faster than another I tend to find there is too much chatter
>> from the client to the server in the first place. The more packets sent
>> back and forth the more likely you will have performance issues under
>> heavy
>> load.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Bob Castleman" <nomail@.here> wrote in message
>> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
>> Why would one choose Named Pipes over TCP/IP for the client net lib? We
>> have
>> noticed for some of our customers that changing to named pipes speeds
>> up
>> the
>> response times of our application. But it is my understanding that a
>> properly configured LAN should work fine with TCP/IP.
>> Thanks,
>> Bob Castleman
>> SuccessWare Software
>>
>>
>>
>|||"Bob Castleman" <nomail@.here> wrote in message
news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> Why would one choose Named Pipes over TCP/IP for the client net lib? We
have
> noticed for some of our customers that changing to named pipes speeds up
the
> response times of our application. But it is my understanding that a
> properly configured LAN should work fine with TCP/IP.
Don't know if this is your problem, but there was a bug a while ago in SQL
Server 2000 to do with the introduction of Kerberos aware authentication
using DBLibrary connections over TCP/IP.
IIRC, the SQL Client and SQL Server would not correctly negotiate packet
sizes. This could be circumvented by explicitly overriding the default
packet size in the client app. This was resolved in SQL Server SP2.
Kind Regards, Howard|||Yes I have seen where issues with DNS have caused situations such as this as
well.
--
Andrew J. Kelly SQL MVP
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:e4O$dsOlEHA.3712@.TK2MSFTNGP15.phx.gbl...
> An other option we see often are a DNS name resolution problems, use
tracert
> to determine the route taken.
> An other thought are these notebooks, that connect wireless as well, or at
> home? In which case it most of the times helps to dump the DNS resolver
> cache of the workstation. See IPCONFIG /flushdns
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2004 All rights reserved.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:e6n8opOlEHA.3372@.TK2MSFTNGP09.phx.gbl...
> > Anti.virus program trying to AV check the named pipes stuff?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Bob Castleman" <nomail@.here> wrote in message
> > news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
> >>A specific example of what we are running into happened this morning. A
> >>customer called complaining of speed problems. Everything was "fine" on
> >>Friday and today it was slow. CPU load on the database server was around
> >>2%. Disks weren't thrasing, etc. I had him change a workstation from
> >>TCP/IP to Named Pipes and the time it took to load our application went
> >>from 20 seconds to 2 seconds. He reported a similar effect on the other
> >>workstations. Nothing I have been able to find about TCP/IP and Named
> >>Pipes would explain this behavior. This seems like some problem in
network
> >>communication, especially since the server was basically sitting idle.
> >>
> >> I personally don't like "quick fixes". We run into this with just
enough
> >> regularity that it would be good to know what's really happening so we
> >> can address the underlying problem.
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
> >> It is my understanding that named pipes have slightly less overhead
than
> >> TCP/IP but there it is usually not enough to warrant using named
pipes.
> >> TCP
> >> is much more versatile and should be encouraged in general over named
> >> pipes.
> >> One thing to note is that WIN2003 has a 25% higher throuput in TCP
over
> >> Win2000 and DNS lookups are up to 120% faster as well. If your
clients
> >> are
> >> looking for speed improvements and are not on Win2003 they should try
it
> >> out. Another thing to note is that usually when clients tell me one
> >> protocol is faster than another I tend to find there is too much
chatter
> >> from the client to the server in the first place. The more packets
sent
> >> back and forth the more likely you will have performance issues under
> >> heavy
> >> load.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Bob Castleman" <nomail@.here> wrote in message
> >> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> >> Why would one choose Named Pipes over TCP/IP for the client net lib?
We
> >> have
> >> noticed for some of our customers that changing to named pipes speeds
> >> up
> >> the
> >> response times of our application. But it is my understanding that a
> >> properly configured LAN should work fine with TCP/IP.
> >>
> >> Thanks,
> >>
> >> Bob Castleman
> >> SuccessWare Software
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>|||Same here.
Basically if the DNS server is unavailable the client will wait for 20-30
seconds, and then resolve the servername in a different way.
You can do a quick check if this is the issue by connecting to the server
via both the IP-address and the servername. IP-address should behave
normally and the servername will have the 30 second delay.
--
Jacco Schalkwijk
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uD8FsIPlEHA.3356@.TK2MSFTNGP14.phx.gbl...
> Yes I have seen where issues with DNS have caused situations such as this
> as
> well.
> --
> Andrew J. Kelly SQL MVP
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:e4O$dsOlEHA.3712@.TK2MSFTNGP15.phx.gbl...
>> An other option we see often are a DNS name resolution problems, use
> tracert
>> to determine the route taken.
>> An other thought are these notebooks, that connect wireless as well, or
>> at
>> home? In which case it most of the times helps to dump the DNS resolver
>> cache of the workstation. See IPCONFIG /flushdns
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2004 All rights reserved.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> message news:e6n8opOlEHA.3372@.TK2MSFTNGP09.phx.gbl...
>> > Anti.virus program trying to AV check the named pipes stuff?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Bob Castleman" <nomail@.here> wrote in message
>> > news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
>> >>A specific example of what we are running into happened this morning. A
>> >>customer called complaining of speed problems. Everything was "fine" on
>> >>Friday and today it was slow. CPU load on the database server was
>> >>around
>> >>2%. Disks weren't thrasing, etc. I had him change a workstation from
>> >>TCP/IP to Named Pipes and the time it took to load our application went
>> >>from 20 seconds to 2 seconds. He reported a similar effect on the other
>> >>workstations. Nothing I have been able to find about TCP/IP and Named
>> >>Pipes would explain this behavior. This seems like some problem in
> network
>> >>communication, especially since the server was basically sitting idle.
>> >>
>> >> I personally don't like "quick fixes". We run into this with just
> enough
>> >> regularity that it would be good to know what's really happening so we
>> >> can address the underlying problem.
>> >>
>> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
>> >> It is my understanding that named pipes have slightly less overhead
> than
>> >> TCP/IP but there it is usually not enough to warrant using named
> pipes.
>> >> TCP
>> >> is much more versatile and should be encouraged in general over named
>> >> pipes.
>> >> One thing to note is that WIN2003 has a 25% higher throuput in TCP
> over
>> >> Win2000 and DNS lookups are up to 120% faster as well. If your
> clients
>> >> are
>> >> looking for speed improvements and are not on Win2003 they should try
> it
>> >> out. Another thing to note is that usually when clients tell me one
>> >> protocol is faster than another I tend to find there is too much
> chatter
>> >> from the client to the server in the first place. The more packets
> sent
>> >> back and forth the more likely you will have performance issues under
>> >> heavy
>> >> load.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Bob Castleman" <nomail@.here> wrote in message
>> >> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
>> >> Why would one choose Named Pipes over TCP/IP for the client net lib?
> We
>> >> have
>> >> noticed for some of our customers that changing to named pipes
>> >> speeds
>> >> up
>> >> the
>> >> response times of our application. But it is my understanding that a
>> >> properly configured LAN should work fine with TCP/IP.
>> >>
>> >> Thanks,
>> >>
>> >> Bob Castleman
>> >> SuccessWare Software
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||Thanks,
I'll try that.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uXQM9FQlEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Same here.
> Basically if the DNS server is unavailable the client will wait for 20-30
> seconds, and then resolve the servername in a different way.
> You can do a quick check if this is the issue by connecting to the server
> via both the IP-address and the servername. IP-address should behave
> normally and the servername will have the 30 second delay.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uD8FsIPlEHA.3356@.TK2MSFTNGP14.phx.gbl...
>> Yes I have seen where issues with DNS have caused situations such as this
>> as
>> well.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
>> news:e4O$dsOlEHA.3712@.TK2MSFTNGP15.phx.gbl...
>> An other option we see often are a DNS name resolution problems, use
>> tracert
>> to determine the route taken.
>> An other thought are these notebooks, that connect wireless as well, or
>> at
>> home? In which case it most of the times helps to dump the DNS resolver
>> cache of the workstation. See IPCONFIG /flushdns
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2004 All rights reserved.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> message news:e6n8opOlEHA.3372@.TK2MSFTNGP09.phx.gbl...
>> > Anti.virus program trying to AV check the named pipes stuff?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Bob Castleman" <nomail@.here> wrote in message
>> > news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
>> >>A specific example of what we are running into happened this morning.
>> >>A
>> >>customer called complaining of speed problems. Everything was "fine"
>> >>on
>> >>Friday and today it was slow. CPU load on the database server was
>> >>around
>> >>2%. Disks weren't thrasing, etc. I had him change a workstation from
>> >>TCP/IP to Named Pipes and the time it took to load our application
>> >>went
>> >>from 20 seconds to 2 seconds. He reported a similar effect on the
>> >>other
>> >>workstations. Nothing I have been able to find about TCP/IP and Named
>> >>Pipes would explain this behavior. This seems like some problem in
>> network
>> >>communication, especially since the server was basically sitting idle.
>> >>
>> >> I personally don't like "quick fixes". We run into this with just
>> enough
>> >> regularity that it would be good to know what's really happening so
>> >> we
>> >> can address the underlying problem.
>> >>
>> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
>> >> It is my understanding that named pipes have slightly less overhead
>> than
>> >> TCP/IP but there it is usually not enough to warrant using named
>> pipes.
>> >> TCP
>> >> is much more versatile and should be encouraged in general over
>> >> named
>> >> pipes.
>> >> One thing to note is that WIN2003 has a 25% higher throuput in TCP
>> over
>> >> Win2000 and DNS lookups are up to 120% faster as well. If your
>> clients
>> >> are
>> >> looking for speed improvements and are not on Win2003 they should
>> >> try
>> it
>> >> out. Another thing to note is that usually when clients tell me one
>> >> protocol is faster than another I tend to find there is too much
>> chatter
>> >> from the client to the server in the first place. The more packets
>> sent
>> >> back and forth the more likely you will have performance issues
>> >> under
>> >> heavy
>> >> load.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Bob Castleman" <nomail@.here> wrote in message
>> >> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
>> >> Why would one choose Named Pipes over TCP/IP for the client net
>> >> lib?
>> We
>> >> have
>> >> noticed for some of our customers that changing to named pipes
>> >> speeds
>> >> up
>> >> the
>> >> response times of our application. But it is my understanding that
>> >> a
>> >> properly configured LAN should work fine with TCP/IP.
>> >>
>> >> Thanks,
>> >>
>> >> Bob Castleman
>> >> SuccessWare Software
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>|||Bob,
Named-pipes is a "netbios" thing so it uses WINS (and LMHOSTS) to resolve
names.
TCP/IP Sockets uses DNS (and HOSTS).
Hence the reason DNS issues affect tcp-ip net-lib and not named-pipes net-lib.
(Even though both fundamentally run over tcp-ip. You can run named-pipes over
other protocols, but that's extremely rare these days)
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faqsql