Different Performance on different XMLA Authentications

Hi,

I am using XMLA to connect to SSAS2005. By using different authentication methods in virtual directory, It returns the same result but different speed.

When I using Anonymous Account IUSR_MachineName, the query will return in 1 seconds. But if i using Integrated Windows Authentication, the query will take approximately 5 seconds to return the result. Any Idea?

I have using SQl server Profiler to trace the step. both of it using same number of steps. The different is if using anonymous, then some of the step is return 0 in duration columns. While if using windows authentication, it will longer. But the result of the step is the same.

So, Any Idea? Or does anyone face it before? Thanks.

The difference is most likely caused by the amount of time that it takes the IIS/SSAS server(s) to talk to the domain controller to check the credentials of the user. With anonymous, it is using a fixed local account for which it does not have to do this. Do you have anyone that could help you check the domain side of things?

Different performance in different machines,,,

Hi All,
I am looking if somebody know how can this happen,,,
I have a couple of stored procedures that I run, one SP call the other.
There are cursor, memory table, and recursive query.
When I run this against a local database in my computer it takes arount 15
secs to return the result and it is just fine
considering the table is about 1 + million records. My PC is P4 512 kb RAM
Now, when I create this SP in the server machine and test them against the
database and run it, it takes more than 10 minutes.
Amazing!! considering is the same table, same indexes, but this server has 4
processors and 1 gig RAM.
What can that be? Can it be a MS SQL configuration?
Thanks in advance
RobertoAre statistics up to date on the server?
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>|||What does you mean with statistic are up to date on the server?
"chris" <chris@.noemail.com> wrote in message
news:%23q$6gDlJEHA.3712@.TK2MSFTNGP09.phx.gbl...
> Are statistics up to date on the server?
>
> "Roberto Martinez" <roberto@.ccubetech.com> wrote in message
> news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> > Hi All,
> >
> > I am looking if somebody know how can this happen,,,
> >
> > I have a couple of stored procedures that I run, one SP call the other.
> > There are cursor, memory table, and recursive query.
> > When I run this against a local database in my computer it takes arount
15
> > secs to return the result and it is just fine
> > considering the table is about 1 + million records. My PC is P4 512 kb
RAM
> >
> > Now, when I create this SP in the server machine and test them against
the
> > database and run it, it takes more than 10 minutes.
> > Amazing!! considering is the same table, same indexes, but this server
has
> 4
> > processors and 1 gig RAM.
> >
> > What can that be? Can it be a MS SQL configuration?
> >
> > Thanks in advance
> >
> > Roberto
> >
> >
>|||Check the query plan for the queries on both machines to ensure the same
indexes are used both places.
If they differ, try doing
update statistics <tablename>
on each table used in the query, and look at the Query plan, and compare
execution times. again..
Lastly ( perhaps) try setting maxdop to 1 for the query on the multi proc
machine and see if performance improves...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>

Different performance in different machines,,,

Hi All,
I am looking if somebody know how can this happen,,,
I have a couple of stored procedures that I run, one SP call the other.
There are cursor, memory table, and recursive query.
When I run this against a local database in my computer it takes arount 15
secs to return the result and it is just fine
considering the table is about 1 + million records. My PC is P4 512 kb RAM
Now, when I create this SP in the server machine and test them against the
database and run it, it takes more than 10 minutes.
Amazing!! considering is the same table, same indexes, but this server has 4
processors and 1 gig RAM.
What can that be? Can it be a MS SQL configuration?
Thanks in advance
RobertoAre statistics up to date on the server?
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>|||What does you mean with statistic are up to date on the server?
"chris" <chris@.noemail.com> wrote in message
news:%23q$6gDlJEHA.3712@.TK2MSFTNGP09.phx.gbl...
> Are statistics up to date on the server?
>
> "Roberto Martinez" <roberto@.ccubetech.com> wrote in message
> news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
15[vbcol=seagreen]
RAM[vbcol=seagreen]
the[vbcol=seagreen]
has[vbcol=seagreen]
> 4
>|||Check the query plan for the queries on both machines to ensure the same
indexes are used both places.
If they differ, try doing
update statistics <tablename>
on each table used in the query, and look at the Query plan, and compare
execution times. again..
Lastly ( perhaps) try setting maxdop to 1 for the query on the multi proc
machine and see if performance improves...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>

Different performance in different machines,,,

Hi All,
I am looking if somebody know how can this happen,,,
I have a couple of stored procedures that I run, one SP call the other.
There are cursor, memory table, and recursive query.
When I run this against a local database in my computer it takes arount 15
secs to return the result and it is just fine
considering the table is about 1 + million records. My PC is P4 512 kb RAM
Now, when I create this SP in the server machine and test them against the
database and run it, it takes more than 10 minutes.
Amazing!! considering is the same table, same indexes, but this server has 4
processors and 1 gig RAM.
What can that be? Can it be a MS SQL configuration?
Thanks in advance
Roberto
Are statistics up to date on the server?
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>
|||What does you mean with statistic are up to date on the server?
"chris" <chris@.noemail.com> wrote in message
news:%23q$6gDlJEHA.3712@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Are statistics up to date on the server?
>
> "Roberto Martinez" <roberto@.ccubetech.com> wrote in message
> news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
15[vbcol=seagreen]
RAM[vbcol=seagreen]
the[vbcol=seagreen]
has
> 4
>
|||Check the query plan for the queries on both machines to ensure the same
indexes are used both places.
If they differ, try doing
update statistics <tablename>
on each table used in the query, and look at the Query plan, and compare
execution times. again..
Lastly ( perhaps) try setting maxdop to 1 for the query on the multi proc
machine and see if performance improves...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>

Different indexes - performance analysis question

Hello

I'm doing some performance analysis for my application. I'm doing 7600 SQL queries based on the following SQL query:

SELECT LocationId, ProductId, BatchId, SUM(Quantity) AS Quantity FROM Logistics WHERE UserId = [number] AND ProductId IN ([productidlist]) GROUP BY LocationId, ProductId, BatchId;

Data in table Logistics has LocationId = 1 and BatchId = 0 for absolute all rows in this test, UserId and ProductId may be different. For each SQL Query it's doing, it's also inserting new rows in the table. The table starts with 0 rows for the first SQL query above, ends with 35 000 rows. Execution for both Editions below is exactly the same (same data inserts)

The graph below is showing the time used in milliseconds (y axis) for each query (x axis) - both editions is doing the exactly the same query but with different indexes.

URL to graph: http://www.lostfields.com/sqlindexing.gif

Edition 2 has the following priority on the PK: UserId, LocationId, ProductId, BatchId
Edition 2 Optimized has the following priority on the PK: UserId, ProductId, BatchId, LocationId.

How come Edition 2 have to scan over a lot more indexes than Edition 2 Optimized? As I can see it this shouldn't have happened since LocationId = 1 all the time. Or am I missing something?

[edit] <img> tag didn't work so I have to just paste the url

What happens if you put locationId = 1 in your query. Its all about selectivity.

What other columns are on the table. I would also look at what happens if you include userid in the group by.

Can you capture the two query plans.

|||

Yes, thank you

It did help a lot to include LocationId = 1 in the query, when I did this in Edition 2 it became just similar to Edition 2 Optimized. I couldn't see any performance increase by including UserId in the GROUP BY.

The other fields/columns are just a TransactionDate (date for the insert) and TransactionId (identity to make it unqiue for stopping a duplicate insert).

The two Execution Plans can be found at http://www.lostfields.com/sqlindexing_plan.gif

I'm not sure why Edition 2 Optimized has a sort method there though, as Edition 2 doesn't, but have a filter (since LocationId isn't in where clause I guess).

Different between ##table and #table regarding performance

hi
also i want to know what have more performance : a table variable or a
temporary table in a stored procedure
the rows in the tables are approx. 1000
thanks michelINF: Frequently Asked Questions - SQL Server 2000 - Table Variables
http://support.microsoft.com/?kbid=305977
AMB
"haenselmic" wrote:

> hi
> also i want to know what have more performance : a table variable or a
> temporary table in a stored procedure
> the rows in the tables are approx. 1000
> thanks michel|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:0E5B6748-F110-497E-ABE3-C9B90F6280FA@.microsoft.com...
> INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
> http://support.microsoft.com/?kbid=305977
>
> AMB
> "haenselmic" wrote:
>
Also I have never found a valid use for an ##temp global temporary table.
David|||You'll generally get better performance from in-memory temporary tables as
long as they are fairly small. SQL Server will push the memory table to
tempdb in certain situations such as row count or available memory. There
are some limitations on indexing with in-memory temporary tables. BOL has a
good section on temporary tables and explains better than I can without
plagairism <g>.
## temporary tables are 'global' temporary tables and are cleaned up when
sql server recycles tempdb. # temporary tables are local to the current
batch and are removed when the batch has completed.
-TIm
"haenselmic" <haenselmic@.discussions.microsoft.com> wrote in message
news:273D07B3-0236-43DC-AEF6-8F6D25681AAA@.microsoft.com...
> hi
> also i want to know what have more performance : a table variable or a
> temporary table in a stored procedure
> the rows in the tables are approx. 1000
> thanks michel|||I have used them a couple of times. It has been a while. From what I can
remember we had to dynamically build several sql statements, insert them
into one (##temporary) table and then perform some data manipulation and
retrieval from the ##table.
Keith Kratochvil
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:egT2TU1eGHA.5088@.TK2MSFTNGP02.phx.gbl...
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:0E5B6748-F110-497E-ABE3-C9B90F6280FA@.microsoft.com...
>
> Also I have never found a valid use for an ##temp global temporary table.
> David
>|||Global temporary tables are cleaned up as follows (according to BOL):
"Global temporary tables are automatically dropped when the session
that created the table ends and all other tasks have stopped
referencing them. The association between a task and a table is
maintained only for the life of a single Transact-SQL statement. This
means that a global temporary table is dropped at the completion of the
last Transact-SQL statement that was actively referencing the table
when the creating session ended."
I didn't think that tempdb recycled to those rules, I thought it was
recreated upon startup only.
Cheers
Will|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:ey10gf1eGHA.2416@.TK2MSFTNGP03.phx.gbl...
>I have used them a couple of times. It has been a while. From what I can
>remember we had to dynamically build several sql statements, insert them
>into one (##temporary) table and then perform some data manipulation and
>retrieval from the ##table.
>
But if you didn't retrieve them in another session, a #table would have
sufficed.
David|||"Tim Dot NoSpam" <Tim.NoSpam@.hughes.net> wrote in message
news:%23Il8tY1eGHA.1208@.TK2MSFTNGP02.phx.gbl...
> You'll generally get better performance from in-memory temporary tables as
> long as they are fairly small. SQL Server will push the memory table to
> tempdb in certain situations such as row count or available memory. There
> are some limitations on indexing with in-memory temporary tables. BOL has
> a good section on temporary tables and explains better than I can without
> plagairism <g>.
> ## temporary tables are 'global' temporary tables and are cleaned up when
> sql server recycles tempdb. # temporary tables are local to the current
> batch and are removed when the batch has completed.
>
#table temp tables live for the life of the connection, not the batch.
There is an exeption for #temp tables created inside stored procedures.
They are automatically dropped after the stored procedure is invoked.
David|||As I mentioned, it has been a while. I don't remember all the gory details,
but the global temp table seemed like the best solution (or perhaps the only
solution) at the time.
Keith Kratochvil
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uGhk1E2eGHA.2188@.TK2MSFTNGP05.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:ey10gf1eGHA.2416@.TK2MSFTNGP03.phx.gbl...
> But if you didn't retrieve them in another session, a #table would have
> sufficed.
> David
>|||Has anyone ever used global temporary tables (##temp)? They seem awfully
useless.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uGhk1E2eGHA.2188@.TK2MSFTNGP05.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:ey10gf1eGHA.2416@.TK2MSFTNGP03.phx.gbl...
> But if you didn't retrieve them in another session, a #table would have
> sufficed.
> David
>

Differences between SQLs 2005 (performance)

Hello every one
I have differences in SQL 2005 in performance. I mean SQL 2005 on the
SBS2003 is slower than SQL 2005 enterprise, or standard (the same hardware
and database (10 GB and 10 user)). On this database it should be the same, I
think. Where should be the reason? Any suggestion?I believe there are some things that EE can do that other versions cannot.
But when you upgraded both, did you update statistics? This is a CRITICAL
step for performance that MANY people miss when they upgrade a database to
2005.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Ostry" <ostry11@.neostrada.pl> wrote in message
news:fb1q69$o5d$1@.atlantis.news.tpi.pl...
> Hello every one
>
> I have differences in SQL 2005 in performance. I mean SQL 2005 on the
> SBS2003 is slower than SQL 2005 enterprise, or standard (the same hardware
> and database (10 GB and 10 user)). On this database it should be the same,
> I think. Where should be the reason? Any suggestion?
>