I have got a query in which a merge join is 99% of the cost ... and I am confused ... is not merge join supposed to be the fastest ? Anyone seen this before ?
Any ideas why this could be happening ... and sorry ... do not ask me to post the code coz I will not be able to ...just because it is 99% of the cost that does not indicate that it was excessive. it just means that out of 100% of total cost required to run the query, (parsing resolving compiling (serial and parallel plans) and execution)
that the execution was 99% of the total operation. this indicates that figuring it out isnt taking a long time but doing it is.
now I believe that when you say 99% of the cost you mean in the Graphical execution plan, then that is still justifiable considering the amount of work that sql has to do to perform a dual sorted join.
sometimes if a merge join is warranted, sql server will sort unsorted columns because the optimizer feels that the merge join is more beneficial to the query than the cost of not performing a merge join.
if one of the columns involved is a foriegn key and it is not indexed you might want to consider a nonclustered index on this column.
this will presort the data in the column and speed up the merge.
the Graphic EX will probably still indicate the 99% (of the process time)but your IO and or execution time might decrease.|||There is a non clustered index on the columns ... but the optimizer is choosing the clustered index ... and the merge join is taking 5 minutes ...|||Is the optimizer scanning the clustered index, or doing a seek? Also, how is the where clause on this pig? If you can eliminate "OR"s or "(NOT) IN"s, you will probably do better.|||The query is on a big fat b#$%%rd table of about 11 million rows ...
Should i take the table with all its where clauses into another temp table and then do a join ... currently I am using a derived table ...|||How about the DDL and the DML...skip the sample data...
Don't forget to script the indexes|||Just read up on "Understanding Merge Joins" in BOL. Sounds like in your case, the data is not sorted on the merge keys going into the query. Also, the fact that you are dealing with millions of rows. I have only seen the merge join a few times, but every time has been with 1 million rows or more.
Just for grins (because I doubt it will work), what is the result of throwing an order by on the subquery? The order by should be the same order as the join, and ideally there should be an index on the table you are joining to with that same order.
And this is just the thing I would need to have someone else look at myself, because you never know what is going to jump out at a different pair of eyes.|||I would avoid the ORDER BY
It's more overhead...until you get the final result (which is how big again?)
It's either going to use the index or it's not.
Where's the driver data coming from, since your doing a merge...
And it will have to scan the driver....|||Another pair of eyes .. i am not gonna get ... am working at client's place all alone on my laptop ... away from my office ... and as I said before in the YAK Corral... that Shark tank story looks a lot like mine ...|||Another pair of eyes .. i am not gonna get
Dude we are your eyes...
Can't you post it?|||Just my luck ... am under a strict NDA|||So am I...just under a completely unrelated project ;-).
As for the sort in the subquery, it is just a guess based on the description of how a merge jin works. It may not work, but it sounds like this is getting down to the "Hail Mary" point.|||Dude we are your eyes...
Anyway ... Thanks for the nice words ...
What do you think would give better performance under the conditions ...
a) Temp table
b) Table variable
c) Derived table
The table contains data for an year ... the selection is for a month ... and then have to make a join on a different col from another big fat table containing about 80,000 records ...|||It's ALL about the indexes...
Get the result set as small as possible...
how many rows in a month of data..if it's not too big you could use a table datatype...
I still like derived tables
In the predicate...is there an index for every column..are they in the right order?
How many columns in the orw?|||How many rows in option 1, 2, or 3?|||No of rows in a month ... 11 million divided by 12 ...
And I too like derived tables ...
not many colums in the row ... if I remember correctly .. 8 or 9 ...
Ok .. going to sleep now ... will update how it goes tomorrow ...|||avoid the order by as much as possible it wont affect the sort order of the table in the case of the join.
i would consider that the merge is going to occur in this case regardless based on the sql optimizer
i would try a join hint (in development only) and initially i would try to set forceplan on. just to see what the io is on your current query as written.
they i would play with the join order just to see how the optimizer chooses it's plan.
oh yeah
dont forget to update stats and check your indicies etc... before you start using optimizer hints.|||avoid the order by
What did I say....
so it's 1 million...not huge, but still alot...
Is the date indexed?
With 8-10 columns the best you'll have would be index seek, bookmark lookup...
Did you SHOW PLAN yet?
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 30, 2012
Merge Join
Hi,
I executed the following query in Northwind:
select c.* from customers c
join orders o on c.customerid=o.customerid
There are original indexes available on these two tables:
1) Clustered (Customers.CustomerID)
2) Clustered (Orders.OrderID)
3) nonClustered (Orders.CustomerID)
The execution plan shows that a nested loop is used to return the result.
But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
Now both tables are sorted based on the join predicate. But still nested
loop happens. I think Merge Join can be more efficient. Why nested loop
still is preferred by query optimizer?
Thanks in advance,
Leila
It depends on how many rows there are. These are pretty small tables, so it
probably sees that it can return the data really fast and returns it. Just
a guess, but they don't keep looking for a plan when optimization would take
more time than running the query like the first plan they see.
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored
"Leila" <leilas@.hotpop.com> wrote in message
news:%23CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>
|||We have found in our software that nested loop joins have far better
performance. In fact, when we get parallelism queries back, we sometimes
will scale up the parallelism cost to 25 from 5 because the query optimizer
"goes stupid" at times.
=-Chris
"Leila" <leilas@.hotpop.com> wrote in message
news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>
|||If two tables are joined, and both tables have a clustered index on the
join key, the a MERGE JOIN is the most efficient join strategy (on
non-SMP systems).
I guess you were actually talking about LOOP JOINs versus HASH JOINs.
Gert-Jan
Christopher Conner wrote:[vbcol=seagreen]
> We have found in our software that nested loop joins have far better
> performance. In fact, when we get parallelism queries back, we sometimes
> will scale up the parallelism cost to 25 from 5 because the query optimizer
> "goes stupid" at times.
> =-Chris
> "Leila" <leilas@.hotpop.com> wrote in message
> news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
I executed the following query in Northwind:
select c.* from customers c
join orders o on c.customerid=o.customerid
There are original indexes available on these two tables:
1) Clustered (Customers.CustomerID)
2) Clustered (Orders.OrderID)
3) nonClustered (Orders.CustomerID)
The execution plan shows that a nested loop is used to return the result.
But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
Now both tables are sorted based on the join predicate. But still nested
loop happens. I think Merge Join can be more efficient. Why nested loop
still is preferred by query optimizer?
Thanks in advance,
Leila
It depends on how many rows there are. These are pretty small tables, so it
probably sees that it can return the data really fast and returns it. Just
a guess, but they don't keep looking for a plan when optimization would take
more time than running the query like the first plan they see.
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored
"Leila" <leilas@.hotpop.com> wrote in message
news:%23CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>
|||We have found in our software that nested loop joins have far better
performance. In fact, when we get parallelism queries back, we sometimes
will scale up the parallelism cost to 25 from 5 because the query optimizer
"goes stupid" at times.
=-Chris
"Leila" <leilas@.hotpop.com> wrote in message
news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>
|||If two tables are joined, and both tables have a clustered index on the
join key, the a MERGE JOIN is the most efficient join strategy (on
non-SMP systems).
I guess you were actually talking about LOOP JOINs versus HASH JOINs.
Gert-Jan
Christopher Conner wrote:[vbcol=seagreen]
> We have found in our software that nested loop joins have far better
> performance. In fact, when we get parallelism queries back, we sometimes
> will scale up the parallelism cost to 25 from 5 because the query optimizer
> "goes stupid" at times.
> =-Chris
> "Leila" <leilas@.hotpop.com> wrote in message
> news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
Merge Join
Hi,
I executed the following query in Northwind:
select c.* from customers c
join orders o on c.customerid=o.customerid
There are original indexes available on these two tables:
1) Clustered (Customers.CustomerID)
2) Clustered (Orders.OrderID)
3) nonClustered (Orders.CustomerID)
The execution plan shows that a nested loop is used to return the result.
But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
Now both tables are sorted based on the join predicate. But still nested
loop happens. I think Merge Join can be more efficient. Why nested loop
still is preferred by query optimizer?
Thanks in advance,
LeilaIt depends on how many rows there are. These are pretty small tables, so it
probably sees that it can return the data really fast and returns it. Just
a guess, but they don't keep looking for a plan when optimization would take
more time than running the query like the first plan they see.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored
"Leila" <leilas@.hotpop.com> wrote in message
news:%23CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||We have found in our software that nested loop joins have far better
performance. In fact, when we get parallelism queries back, we sometimes
will scale up the parallelism cost to 25 from 5 because the query optimizer
"goes stupid" at times.
=-Chris
"Leila" <leilas@.hotpop.com> wrote in message
news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||If two tables are joined, and both tables have a clustered index on the
join key, the a MERGE JOIN is the most efficient join strategy (on
non-SMP systems).
I guess you were actually talking about LOOP JOINs versus HASH JOINs.
Gert-Jan
Christopher Conner wrote:[vbcol=seagreen]
> We have found in our software that nested loop joins have far better
> performance. In fact, when we get parallelism queries back, we sometimes
> will scale up the parallelism cost to 25 from 5 because the query optimize
r
> "goes stupid" at times.
> =-Chris
> "Leila" <leilas@.hotpop.com> wrote in message
> news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
I executed the following query in Northwind:
select c.* from customers c
join orders o on c.customerid=o.customerid
There are original indexes available on these two tables:
1) Clustered (Customers.CustomerID)
2) Clustered (Orders.OrderID)
3) nonClustered (Orders.CustomerID)
The execution plan shows that a nested loop is used to return the result.
But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
Now both tables are sorted based on the join predicate. But still nested
loop happens. I think Merge Join can be more efficient. Why nested loop
still is preferred by query optimizer?
Thanks in advance,
LeilaIt depends on how many rows there are. These are pretty small tables, so it
probably sees that it can return the data really fast and returns it. Just
a guess, but they don't keep looking for a plan when optimization would take
more time than running the query like the first plan they see.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored
"Leila" <leilas@.hotpop.com> wrote in message
news:%23CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||We have found in our software that nested loop joins have far better
performance. In fact, when we get parallelism queries back, we sometimes
will scale up the parallelism cost to 25 from 5 because the query optimizer
"goes stupid" at times.
=-Chris
"Leila" <leilas@.hotpop.com> wrote in message
news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||If two tables are joined, and both tables have a clustered index on the
join key, the a MERGE JOIN is the most efficient join strategy (on
non-SMP systems).
I guess you were actually talking about LOOP JOINs versus HASH JOINs.
Gert-Jan
Christopher Conner wrote:[vbcol=seagreen]
> We have found in our software that nested loop joins have far better
> performance. In fact, when we get parallelism queries back, we sometimes
> will scale up the parallelism cost to 25 from 5 because the query optimize
r
> "goes stupid" at times.
> =-Chris
> "Leila" <leilas@.hotpop.com> wrote in message
> news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
Merge Join
Hi,
I executed the following query in Northwind:
select c.* from customers c
join orders o on c.customerid=o.customerid
There are original indexes available on these two tables:
1) Clustered (Customers.CustomerID)
2) Clustered (Orders.OrderID)
3) nonClustered (Orders.CustomerID)
The execution plan shows that a nested loop is used to return the result.
But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
Now both tables are sorted based on the join predicate. But still nested
loop happens. I think Merge Join can be more efficient. Why nested loop
still is preferred by query optimizer?
Thanks in advance,
LeilaIt depends on how many rows there are. These are pretty small tables, so it
probably sees that it can return the data really fast and returns it. Just
a guess, but they don't keep looking for a plan when optimization would take
more time than running the query like the first plan they see.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Leila" <leilas@.hotpop.com> wrote in message
news:%23CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||We have found in our software that nested loop joins have far better
performance. In fact, when we get parallelism queries back, we sometimes
will scale up the parallelism cost to 25 from 5 because the query optimizer
"goes stupid" at times.
=-Chris
"Leila" <leilas@.hotpop.com> wrote in message
news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||If two tables are joined, and both tables have a clustered index on the
join key, the a MERGE JOIN is the most efficient join strategy (on
non-SMP systems).
I guess you were actually talking about LOOP JOINs versus HASH JOINs.
Gert-Jan
Christopher Conner wrote:
> We have found in our software that nested loop joins have far better
> performance. In fact, when we get parallelism queries back, we sometimes
> will scale up the parallelism cost to 25 from 5 because the query optimizer
> "goes stupid" at times.
> =-Chris
> "Leila" <leilas@.hotpop.com> wrote in message
> news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > I executed the following query in Northwind:
> >
> > select c.* from customers c
> > join orders o on c.customerid=o.customerid
> >
> > There are original indexes available on these two tables:
> >
> > 1) Clustered (Customers.CustomerID)
> > 2) Clustered (Orders.OrderID)
> > 3) nonClustered (Orders.CustomerID)
> >
> > The execution plan shows that a nested loop is used to return the result.
> > But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> > Now both tables are sorted based on the join predicate. But still nested
> > loop happens. I think Merge Join can be more efficient. Why nested loop
> > still is preferred by query optimizer?
> > Thanks in advance,
> > Leila
> >
> >sql
I executed the following query in Northwind:
select c.* from customers c
join orders o on c.customerid=o.customerid
There are original indexes available on these two tables:
1) Clustered (Customers.CustomerID)
2) Clustered (Orders.OrderID)
3) nonClustered (Orders.CustomerID)
The execution plan shows that a nested loop is used to return the result.
But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
Now both tables are sorted based on the join predicate. But still nested
loop happens. I think Merge Join can be more efficient. Why nested loop
still is preferred by query optimizer?
Thanks in advance,
LeilaIt depends on how many rows there are. These are pretty small tables, so it
probably sees that it can return the data really fast and returns it. Just
a guess, but they don't keep looking for a plan when optimization would take
more time than running the query like the first plan they see.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Leila" <leilas@.hotpop.com> wrote in message
news:%23CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||We have found in our software that nested loop joins have far better
performance. In fact, when we get parallelism queries back, we sometimes
will scale up the parallelism cost to 25 from 5 because the query optimizer
"goes stupid" at times.
=-Chris
"Leila" <leilas@.hotpop.com> wrote in message
news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||If two tables are joined, and both tables have a clustered index on the
join key, the a MERGE JOIN is the most efficient join strategy (on
non-SMP systems).
I guess you were actually talking about LOOP JOINs versus HASH JOINs.
Gert-Jan
Christopher Conner wrote:
> We have found in our software that nested loop joins have far better
> performance. In fact, when we get parallelism queries back, we sometimes
> will scale up the parallelism cost to 25 from 5 because the query optimizer
> "goes stupid" at times.
> =-Chris
> "Leila" <leilas@.hotpop.com> wrote in message
> news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > I executed the following query in Northwind:
> >
> > select c.* from customers c
> > join orders o on c.customerid=o.customerid
> >
> > There are original indexes available on these two tables:
> >
> > 1) Clustered (Customers.CustomerID)
> > 2) Clustered (Orders.OrderID)
> > 3) nonClustered (Orders.CustomerID)
> >
> > The execution plan shows that a nested loop is used to return the result.
> > But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> > Now both tables are sorted based on the join predicate. But still nested
> > loop happens. I think Merge Join can be more efficient. Why nested loop
> > still is preferred by query optimizer?
> > Thanks in advance,
> > Leila
> >
> >sql
Wednesday, March 28, 2012
Merge Different Data Types in Query
I need to merge a numeric field (ID) with the varchar field (description) in
my query. Any idea how I can do this?
ID DESC
22 Text here
20 Text Here
Query Date to be: "(22) Text"
Thanks in advance!!You can use CAST, CONVERT or STR function to do this. For details of these
function, pl. refer to SQL Server Books Online.
SELECT '"' + CAST( id AS VARCHAR ) + SPACE( 3 ) +
desc + '"'
FROM tbl ;
Anith|||Anith,
This is awesome. I am looking into this function as we speak. However, I
ran your suggestion and it does exactly what I want except it has 'Select '
before the data as part of the field. Anyway around this?
Example:
"Select 1999 Short Description" instead of "1999 short description"
"Anith Sen" wrote:
> You can use CAST, CONVERT or STR function to do this. For details of these
> function, pl. refer to SQL Server Books Online.
> SELECT '"' + CAST( id AS VARCHAR ) + SPACE( 3 ) +
> desc + '"'
> FROM tbl ;
> --
> Anith
>
>|||I think you have quotes messed up somewhere. Without a table DDL & sample
data it is hard to point out the issue. Also it may have something to do
with the wrapper code/interface/client programming language you may be
using. Here is an example to check it out in Query Analyzer:
CREATE TABLE tbl ( id_ INT NOT NULL PRIMARY KEY,
descr_ VARCHAR(50) NOT NULL );
INSERT tbl SELECT 1999, 'short description' ;
SELECT '"' + CAST( id_ AS VARCHAR ) + SPACE( 3 ) + descr_ + '"'
FROM tbl ;
Anith|||Here is the Query I'm using in the view:
SELECT DISTINCT
TOP 100 PERCENT dbo.IssueSubType.IssueSubTypeID,
dbo.IssueSubType.IssueSubTypeTitle, dbo.IssueType.IssueTypeID,
'SELECT ' + CAST(dbo.Issue.IssueID AS VarChar) +
SPACE(2) + dbo.Issue.ShortDescription + '' AS Expr1, dbo.Issue.IssueID
FROM dbo.IssueType INNER JOIN
dbo.IssueSubType ON dbo.IssueType.IssueTypeID =
dbo.IssueSubType.IssueTypeID INNER JOIN
dbo.Issue ON dbo.IssueType.IssueTypeID =
dbo.Issue.IssueTypeID AND dbo.IssueSubType.IssueSubTypeID =
dbo.Issue.IssueSubTypeID
WHERE (dbo.IssueType.IssueTypeID = 16)
ORDER BY dbo.IssueSubType.IssueSubTypeID
"Anith Sen" wrote:
> I think you have quotes messed up somewhere. Without a table DDL & sample
> data it is hard to point out the issue. Also it may have something to do
> with the wrapper code/interface/client programming language you may be
> using. Here is an example to check it out in Query Analyzer:
> CREATE TABLE tbl ( id_ INT NOT NULL PRIMARY KEY,
> descr_ VARCHAR(50) NOT NULL );
> INSERT tbl SELECT 1999, 'short description' ;
> SELECT '"' + CAST( id_ AS VARCHAR ) + SPACE( 3 ) + descr_ + '"'
> FROM tbl ;
> --
> Anith
>
>|||Remove the part >> 'SELECT ' + << from your query.
Also, as a suggestion, remove TOP 100 & ORDER BY from the view. It offers
nothing beneficial & could cause optimization issues when used in complex
queries.
If an ordered resultset is the intention, consider using ORDER BY clause in
the query using the view instead.
Anith|||That was it. I tried various combinations of that but never that. Thanks
for your help!!!
"Anith Sen" wrote:
> Remove the part >> 'SELECT ' + << from your query.
> Also, as a suggestion, remove TOP 100 & ORDER BY from the view. It offers
> nothing beneficial & could cause optimization issues when used in complex
> queries.
> If an ordered resultset is the intention, consider using ORDER BY clause i
n
> the query using the view instead.
> --
> Anith
>
>
my query. Any idea how I can do this?
ID DESC
22 Text here
20 Text Here
Query Date to be: "(22) Text"
Thanks in advance!!You can use CAST, CONVERT or STR function to do this. For details of these
function, pl. refer to SQL Server Books Online.
SELECT '"' + CAST( id AS VARCHAR ) + SPACE( 3 ) +
desc + '"'
FROM tbl ;
Anith|||Anith,
This is awesome. I am looking into this function as we speak. However, I
ran your suggestion and it does exactly what I want except it has 'Select '
before the data as part of the field. Anyway around this?
Example:
"Select 1999 Short Description" instead of "1999 short description"
"Anith Sen" wrote:
> You can use CAST, CONVERT or STR function to do this. For details of these
> function, pl. refer to SQL Server Books Online.
> SELECT '"' + CAST( id AS VARCHAR ) + SPACE( 3 ) +
> desc + '"'
> FROM tbl ;
> --
> Anith
>
>|||I think you have quotes messed up somewhere. Without a table DDL & sample
data it is hard to point out the issue. Also it may have something to do
with the wrapper code/interface/client programming language you may be
using. Here is an example to check it out in Query Analyzer:
CREATE TABLE tbl ( id_ INT NOT NULL PRIMARY KEY,
descr_ VARCHAR(50) NOT NULL );
INSERT tbl SELECT 1999, 'short description' ;
SELECT '"' + CAST( id_ AS VARCHAR ) + SPACE( 3 ) + descr_ + '"'
FROM tbl ;
Anith|||Here is the Query I'm using in the view:
SELECT DISTINCT
TOP 100 PERCENT dbo.IssueSubType.IssueSubTypeID,
dbo.IssueSubType.IssueSubTypeTitle, dbo.IssueType.IssueTypeID,
'SELECT ' + CAST(dbo.Issue.IssueID AS VarChar) +
SPACE(2) + dbo.Issue.ShortDescription + '' AS Expr1, dbo.Issue.IssueID
FROM dbo.IssueType INNER JOIN
dbo.IssueSubType ON dbo.IssueType.IssueTypeID =
dbo.IssueSubType.IssueTypeID INNER JOIN
dbo.Issue ON dbo.IssueType.IssueTypeID =
dbo.Issue.IssueTypeID AND dbo.IssueSubType.IssueSubTypeID =
dbo.Issue.IssueSubTypeID
WHERE (dbo.IssueType.IssueTypeID = 16)
ORDER BY dbo.IssueSubType.IssueSubTypeID
"Anith Sen" wrote:
> I think you have quotes messed up somewhere. Without a table DDL & sample
> data it is hard to point out the issue. Also it may have something to do
> with the wrapper code/interface/client programming language you may be
> using. Here is an example to check it out in Query Analyzer:
> CREATE TABLE tbl ( id_ INT NOT NULL PRIMARY KEY,
> descr_ VARCHAR(50) NOT NULL );
> INSERT tbl SELECT 1999, 'short description' ;
> SELECT '"' + CAST( id_ AS VARCHAR ) + SPACE( 3 ) + descr_ + '"'
> FROM tbl ;
> --
> Anith
>
>|||Remove the part >> 'SELECT ' + << from your query.
Also, as a suggestion, remove TOP 100 & ORDER BY from the view. It offers
nothing beneficial & could cause optimization issues when used in complex
queries.
If an ordered resultset is the intention, consider using ORDER BY clause in
the query using the view instead.
Anith|||That was it. I tried various combinations of that but never that. Thanks
for your help!!!
"Anith Sen" wrote:
> Remove the part >> 'SELECT ' + << from your query.
> Also, as a suggestion, remove TOP 100 & ORDER BY from the view. It offers
> nothing beneficial & could cause optimization issues when used in complex
> queries.
> If an ordered resultset is the intention, consider using ORDER BY clause i
n
> the query using the view instead.
> --
> Anith
>
>
Merge apparent duplcate rows into 1 row?
Hi,
I have a select query that can generate apparent duplicates; this
occurs because the Histology value is determined from a table
tblSample, this may contain a number of samples for the same location
as there are different methods of obtaining samples sometimes 2 or more
method are used to back up results. The method is not important for
this table and so not shown, so showing apparent duplicates. Heres an
example of the table:
Code Date Location Histology
---
CO123 12/08/2005 Left Main Adeno
CO123 12/08/2005 Left Main Adeno
BJ234 12/08/2005 Right Main Normal
BJ234 12/08/2005 Right Lower Squamous
CH345 17/08/2005 Right Middle Normal
This is my SQL:
SELECT tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Is there a way to combine these apparent duplicate rows into one row?
Essentially doing:
If no of rows where Code, Date, Location, match > 1
Delete rows >= 2
ThanksUse a SELECT DISTINCT:
SELECT DISTINCT
tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1125055041.994620.74900@.g14g2000cwa.googlegroups.com...
Hi,
I have a select query that can generate apparent duplicates; this
occurs because the Histology value is determined from a table
tblSample, this may contain a number of samples for the same location
as there are different methods of obtaining samples sometimes 2 or more
method are used to back up results. The method is not important for
this table and so not shown, so showing apparent duplicates. Heres an
example of the table:
Code Date Location Histology
---
CO123 12/08/2005 Left Main Adeno
CO123 12/08/2005 Left Main Adeno
BJ234 12/08/2005 Right Main Normal
BJ234 12/08/2005 Right Lower Squamous
CH345 17/08/2005 Right Middle Normal
This is my SQL:
SELECT tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Is there a way to combine these apparent duplicate rows into one row?
Essentially doing:
If no of rows where Code, Date, Location, match > 1
Delete rows >= 2
Thanks|||Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!|||If you're new to SQL, this is the place to hang out. :-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1125058223.511501.191000@.o13g2000cwo.googlegroups.com...
Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!
I have a select query that can generate apparent duplicates; this
occurs because the Histology value is determined from a table
tblSample, this may contain a number of samples for the same location
as there are different methods of obtaining samples sometimes 2 or more
method are used to back up results. The method is not important for
this table and so not shown, so showing apparent duplicates. Heres an
example of the table:
Code Date Location Histology
---
CO123 12/08/2005 Left Main Adeno
CO123 12/08/2005 Left Main Adeno
BJ234 12/08/2005 Right Main Normal
BJ234 12/08/2005 Right Lower Squamous
CH345 17/08/2005 Right Middle Normal
This is my SQL:
SELECT tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Is there a way to combine these apparent duplicate rows into one row?
Essentially doing:
If no of rows where Code, Date, Location, match > 1
Delete rows >= 2
ThanksUse a SELECT DISTINCT:
SELECT DISTINCT
tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1125055041.994620.74900@.g14g2000cwa.googlegroups.com...
Hi,
I have a select query that can generate apparent duplicates; this
occurs because the Histology value is determined from a table
tblSample, this may contain a number of samples for the same location
as there are different methods of obtaining samples sometimes 2 or more
method are used to back up results. The method is not important for
this table and so not shown, so showing apparent duplicates. Heres an
example of the table:
Code Date Location Histology
---
CO123 12/08/2005 Left Main Adeno
CO123 12/08/2005 Left Main Adeno
BJ234 12/08/2005 Right Main Normal
BJ234 12/08/2005 Right Lower Squamous
CH345 17/08/2005 Right Middle Normal
This is my SQL:
SELECT tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Is there a way to combine these apparent duplicate rows into one row?
Essentially doing:
If no of rows where Code, Date, Location, match > 1
Delete rows >= 2
Thanks|||Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!|||If you're new to SQL, this is the place to hang out. :-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1125058223.511501.191000@.o13g2000cwo.googlegroups.com...
Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!
Friday, March 23, 2012
Mental Block on SQL query. Joining one to many with one of the many
Sorry for the akward title, not sure how to say this. I have a Person table and a addresses table. Each person may have many address records as shown below:
Person
--
PersonID | AutoNum
fName | varchar
lName | varchar
...etc
Addresses
addressID | AutoNum
personID | int (FK)
address1 | varchar
city | varchar
state | varchar
isPrimary | bit
...etc
What I'm trying to do is select all from Person and the city and state of each person's primary address. A little voice keeps saying subquery...but I can't figure it out. So far I have the SQL below, but if there is no address or no address where isPrimary = 1, it fails to return the person record. I need the person record regardless of if they have a primary address. Does that make sense?
Doesn't return all Person records:
SELECT Person.*, Address.City, Address.State
FROM Person LEFT OUTER JOIN Address
ON Person.PersonID = Address.PersonID
WHERE (Address.isPrimary= 1)
ORDER BY Person.lName, Person.fName
Try:
Code Snippet
SELECT Person.*, Address.City, Address.State, Address.isPrimary
FROM Person LEFT OUTER JOIN Address
ON Person.PersonID = Address.PersonID
AND (Address.isPrimary= 1)
ORDER BY Person.lName, Person.fName
Wednesday, March 21, 2012
Mental Block
Can some please help with this SQL problem:
I have tables in select query and i want to show all
dbo.TblTerritory.Description even is there is no data in the other columns,
ie there are no records of type 'lost' in ProjectStatus
SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname, CONVERT(int,
CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr)) AS dtsort
FROM dbo.jp_tblproject_stats INNER JOIN
dbo.tblCalendar_jp ON dbo.jp_tblproject_stats.Mnth =
dbo.tblCalendar_jpM RIGHT OUTER JOIN
dbo.TblTerritory ON
dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
(dbo.jp_tblproject_stats.ProjectStatus = 'lost')
GROUP BY dbo.TblTerritory.Description, dbo.jp_tblproject_stats.Counter,
dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr))
ORDER BY dbo.TblTerritory.Description, CONVERT(int, CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr))
pleae help
Regards
John"John" <topguy75@.hotmail.com> wrote in message
news:437395f5$0$23285$db0fefd9@.news.zen.co.uk...
> Can some please help with this SQL problem:
> I have tables in select query and i want to show all
> dbo.TblTerritory.Description even is there is no data in the other
columns,
> ie there are no records of type 'lost' in ProjectStatus
> SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname,
CONVERT(int,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) +
CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats INNER JOIN
> dbo.tblCalendar_jp ON
dbo.jp_tblproject_stats.Mnth =
> dbo.tblCalendar_jpM RIGHT OUTER JOIN
> dbo.TblTerritory ON
> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
> WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
> GROUP BY dbo.TblTerritory.Description,
dbo.jp_tblproject_stats.Counter,
> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) +
CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> ORDER BY dbo.TblTerritory.Description, CONVERT(int,
CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> pleae help
> Regards
> John
>
John,
-- The query realigned and aliased for readability:
SELECT TOP 100 PERCENT
T1.Description
,PS1.Counter
,C1.monthname
,CONVERT(int, CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr)) AS dtsort
FROM dbo.jp_tblproject_stats AS PS1
INNER JOIN
dbo.tblCalendar_jp AS C1
-- Note right here there is no column name
-- On the right-hand side of the = operator,
-- just the table alias.
ON PS1.Mnth = C1
RIGHT OUTER JOIN
dbo.TblTerritory AS T1
ON PS1.Territory_Code = T1.Code
WHERE (T1.Manager = 'clive wallom')
AND (PS1.ProjectStatus = 'lost')
GROUP BY T1.Description
,PS1.Counter
,C1.monthname
,CONVERT(int ,CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr))
ORDER BY T1.Description
,CONVERT(int, CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr))
The above originally appeared as:
FROM dbo.jp_tblproject_stats
INNER JOIN
dbo.tblCalendar_jp
ON dbo.jp_tblproject_stats.Mnth
= dbo.tblCalendar_jpM
-- .<some column name goes here>
-- Probably should be .Mnth
Sincerely,
Chris O.|||John:
When there is a requirement of having all row returned for a particular
column no matter if it has records in other tables you are joining to, one
should be very careful in putting in the where clause.
When your where clause is not properly designed, it will filter out the rows
having null value in that column. You can modify your where clause to let it
include null values or you can put these condition while you join two tables
.
(the way Chris suggested you)
Putting this in ON conditions while you are joining two table at times might
become quite complex.
An easier way would be to modify the where clause to let it include the null
s
say for example:
instead of ( dbo.TblTerritory.Manager = 'clive wallom' )
you cna put ( dbo.TblTerritory.Manager = 'clive wallom'
or dbo.TblTerritory.Manager is null)
Either of the approach will give you expected result and will perform equall
y.
Abhishek
"John" wrote:
> Can some please help with this SQL problem:
> I have tables in select query and i want to show all
> dbo.TblTerritory.Description even is there is no data in the other columns
,
> ie there are no records of type 'lost' in ProjectStatus
> SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname, CONVERT(int
,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats INNER JOIN
> dbo.tblCalendar_jp ON dbo.jp_tblproject_stats.Mnth =
> dbo.tblCalendar_jpM RIGHT OUTER JOIN
> dbo.TblTerritory ON
> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
> WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
> GROUP BY dbo.TblTerritory.Description, dbo.jp_tblproject_stats.Counter,
> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> ORDER BY dbo.TblTerritory.Description, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> pleae help
> Regards
> John
>
>|||Thanks for your assistance, suprising what a bit of organising acheives. it
was '.m'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:vaKdnYt0T5kyIu7eRVn-sg@.comcast.com...
> "John" <topguy75@.hotmail.com> wrote in message
> news:437395f5$0$23285$db0fefd9@.news.zen.co.uk...
> columns,
> CONVERT(int,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth =
> dbo.jp_tblproject_stats.Counter,
> CONVERT(varchar,
> CONVERT(varchar,
> John,
> -- The query realigned and aliased for readability:
> SELECT TOP 100 PERCENT
> T1.Description
> ,PS1.Counter
> ,C1.monthname
> ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats AS PS1
> INNER JOIN
> dbo.tblCalendar_jp AS C1
> -- Note right here there is no column name
> -- On the right-hand side of the = operator,
> -- just the table alias.
> ON PS1.Mnth = C1
> RIGHT OUTER JOIN
> dbo.TblTerritory AS T1
> ON PS1.Territory_Code = T1.Code
> WHERE (T1.Manager = 'clive wallom')
> AND (PS1.ProjectStatus = 'lost')
> GROUP BY T1.Description
> ,PS1.Counter
> ,C1.monthname
> ,CONVERT(int ,CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr))
> ORDER BY T1.Description
> ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr))
> The above originally appeared as:
> FROM dbo.jp_tblproject_stats
> INNER JOIN
> dbo.tblCalendar_jp
> ON dbo.jp_tblproject_stats.Mnth
> = dbo.tblCalendar_jpM
> -- .<some column name goes here>
> -- Probably should be .Mnth
> Sincerely,
> Chris O.
>
I have tables in select query and i want to show all
dbo.TblTerritory.Description even is there is no data in the other columns,
ie there are no records of type 'lost' in ProjectStatus
SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname, CONVERT(int,
CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr)) AS dtsort
FROM dbo.jp_tblproject_stats INNER JOIN
dbo.tblCalendar_jp ON dbo.jp_tblproject_stats.Mnth =
dbo.tblCalendar_jpM RIGHT OUTER JOIN
dbo.TblTerritory ON
dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
(dbo.jp_tblproject_stats.ProjectStatus = 'lost')
GROUP BY dbo.TblTerritory.Description, dbo.jp_tblproject_stats.Counter,
dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr))
ORDER BY dbo.TblTerritory.Description, CONVERT(int, CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr))
pleae help
Regards
John"John" <topguy75@.hotmail.com> wrote in message
news:437395f5$0$23285$db0fefd9@.news.zen.co.uk...
> Can some please help with this SQL problem:
> I have tables in select query and i want to show all
> dbo.TblTerritory.Description even is there is no data in the other
columns,
> ie there are no records of type 'lost' in ProjectStatus
> SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname,
CONVERT(int,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) +
CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats INNER JOIN
> dbo.tblCalendar_jp ON
dbo.jp_tblproject_stats.Mnth =
> dbo.tblCalendar_jpM RIGHT OUTER JOIN
> dbo.TblTerritory ON
> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
> WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
> GROUP BY dbo.TblTerritory.Description,
dbo.jp_tblproject_stats.Counter,
> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) +
CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> ORDER BY dbo.TblTerritory.Description, CONVERT(int,
CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> pleae help
> Regards
> John
>
John,
-- The query realigned and aliased for readability:
SELECT TOP 100 PERCENT
T1.Description
,PS1.Counter
,C1.monthname
,CONVERT(int, CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr)) AS dtsort
FROM dbo.jp_tblproject_stats AS PS1
INNER JOIN
dbo.tblCalendar_jp AS C1
-- Note right here there is no column name
-- On the right-hand side of the = operator,
-- just the table alias.
ON PS1.Mnth = C1
RIGHT OUTER JOIN
dbo.TblTerritory AS T1
ON PS1.Territory_Code = T1.Code
WHERE (T1.Manager = 'clive wallom')
AND (PS1.ProjectStatus = 'lost')
GROUP BY T1.Description
,PS1.Counter
,C1.monthname
,CONVERT(int ,CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr))
ORDER BY T1.Description
,CONVERT(int, CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr))
The above originally appeared as:
FROM dbo.jp_tblproject_stats
INNER JOIN
dbo.tblCalendar_jp
ON dbo.jp_tblproject_stats.Mnth
= dbo.tblCalendar_jpM
-- .<some column name goes here>
-- Probably should be .Mnth
Sincerely,
Chris O.|||John:
When there is a requirement of having all row returned for a particular
column no matter if it has records in other tables you are joining to, one
should be very careful in putting in the where clause.
When your where clause is not properly designed, it will filter out the rows
having null value in that column. You can modify your where clause to let it
include null values or you can put these condition while you join two tables
.
(the way Chris suggested you)
Putting this in ON conditions while you are joining two table at times might
become quite complex.
An easier way would be to modify the where clause to let it include the null
s
say for example:
instead of ( dbo.TblTerritory.Manager = 'clive wallom' )
you cna put ( dbo.TblTerritory.Manager = 'clive wallom'
or dbo.TblTerritory.Manager is null)
Either of the approach will give you expected result and will perform equall
y.
Abhishek
"John" wrote:
> Can some please help with this SQL problem:
> I have tables in select query and i want to show all
> dbo.TblTerritory.Description even is there is no data in the other columns
,
> ie there are no records of type 'lost' in ProjectStatus
> SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname, CONVERT(int
,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats INNER JOIN
> dbo.tblCalendar_jp ON dbo.jp_tblproject_stats.Mnth =
> dbo.tblCalendar_jpM RIGHT OUTER JOIN
> dbo.TblTerritory ON
> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
> WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
> GROUP BY dbo.TblTerritory.Description, dbo.jp_tblproject_stats.Counter,
> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> ORDER BY dbo.TblTerritory.Description, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> pleae help
> Regards
> John
>
>|||Thanks for your assistance, suprising what a bit of organising acheives. it
was '.m'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:vaKdnYt0T5kyIu7eRVn-sg@.comcast.com...
> "John" <topguy75@.hotmail.com> wrote in message
> news:437395f5$0$23285$db0fefd9@.news.zen.co.uk...
> columns,
> CONVERT(int,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth =
> dbo.jp_tblproject_stats.Counter,
> CONVERT(varchar,
> CONVERT(varchar,
> John,
> -- The query realigned and aliased for readability:
> SELECT TOP 100 PERCENT
> T1.Description
> ,PS1.Counter
> ,C1.monthname
> ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats AS PS1
> INNER JOIN
> dbo.tblCalendar_jp AS C1
> -- Note right here there is no column name
> -- On the right-hand side of the = operator,
> -- just the table alias.
> ON PS1.Mnth = C1
> RIGHT OUTER JOIN
> dbo.TblTerritory AS T1
> ON PS1.Territory_Code = T1.Code
> WHERE (T1.Manager = 'clive wallom')
> AND (PS1.ProjectStatus = 'lost')
> GROUP BY T1.Description
> ,PS1.Counter
> ,C1.monthname
> ,CONVERT(int ,CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr))
> ORDER BY T1.Description
> ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr))
> The above originally appeared as:
> FROM dbo.jp_tblproject_stats
> INNER JOIN
> dbo.tblCalendar_jp
> ON dbo.jp_tblproject_stats.Mnth
> = dbo.tblCalendar_jpM
> -- .<some column name goes here>
> -- Probably should be .Mnth
> Sincerely,
> Chris O.
>
Monday, March 12, 2012
Memory Usage
One of my friend's question:
"When I get a big result by a query (such as 1M of rows), the memory usage
of my system decreasing critically. What is the phenomenon that causes this
behaviour?"
Ok it is usual, because I can still observe the rows in the result pane
below. But, Although I close the pane, the mem.usage is still high.
I think it occurs because of the cached result in order to respond later
requests in a fast manner.
Can you please make a comment about the subject?
AND, I would like to learn if there is a way to release/flush this memory by
the Query Analyzer.That happens by design. It's not a flaw or memory leakage.
It is really not the Query Analyser that is "eating the memory", but
instead SQL Server that is behaving according to the Memory settings
established on Enterprise Manager.
Open EM, right click the server registration you wish to check and
click Properties. Move to the memory tab. It is recommended that the
server is configured to dynamically allocate memory.
SQL Server will use as much memory as it can. As other process may
start requesting memory, it will release it. Untill then, it will keep
his own memory usage. With queries being run on the Query Analyser, the
memory usage will keep increasing just short of having to start paging.
I know of no way to programatically release this memory. However, I
don't think this is an issue, since any other process that needs memory
will have it released by SQL Server.
"When I get a big result by a query (such as 1M of rows), the memory usage
of my system decreasing critically. What is the phenomenon that causes this
behaviour?"
Ok it is usual, because I can still observe the rows in the result pane
below. But, Although I close the pane, the mem.usage is still high.
I think it occurs because of the cached result in order to respond later
requests in a fast manner.
Can you please make a comment about the subject?
AND, I would like to learn if there is a way to release/flush this memory by
the Query Analyzer.That happens by design. It's not a flaw or memory leakage.
It is really not the Query Analyser that is "eating the memory", but
instead SQL Server that is behaving according to the Memory settings
established on Enterprise Manager.
Open EM, right click the server registration you wish to check and
click Properties. Move to the memory tab. It is recommended that the
server is configured to dynamically allocate memory.
SQL Server will use as much memory as it can. As other process may
start requesting memory, it will release it. Untill then, it will keep
his own memory usage. With queries being run on the Query Analyser, the
memory usage will keep increasing just short of having to start paging.
I know of no way to programatically release this memory. However, I
don't think this is an issue, since any other process that needs memory
will have it released by SQL Server.
Friday, March 9, 2012
Memory Question
Hi,
Does SQL Server (Developer Edition, on XP Pro) ever release memory space
after processing a query? I have a query that runs against a database, 3 Gig
in size. And the entire process costs 500M in memory usage. So every time
after I run it, my system (as mentioned) just slows down to the point I have
to restart the sqlserver.exe to release that 500M memory space. Any idea?
Thx in advance!SQL Server releases memory only on OS request. Don't restart the service, if
any other application needs memory, it gets it. This is by design, because
SQL Server caches data and execution plans, so it can work faster.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!|||Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3 Gig
> in size. And the entire process costs 500M in memory usage. So every time
> after I run it, my system (as mentioned) just slows down to the point I have
> to restart the sqlserver.exe to release that 500M memory space. Any idea?
> Thx in advance!|||In addition to the other comments if you only have about 500MB I suggest you
set the MAX Memory setting in SQL Server to less than the max to try and
always leave some memory for the OS and other apps you run on your XP
machine. While SQL Server will release some memory when they demand it the
process can get a little painful on a workstation where lots of other things
are going on and there is little memory.
--
Andrew J. Kelly SQL MVP
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!
Does SQL Server (Developer Edition, on XP Pro) ever release memory space
after processing a query? I have a query that runs against a database, 3 Gig
in size. And the entire process costs 500M in memory usage. So every time
after I run it, my system (as mentioned) just slows down to the point I have
to restart the sqlserver.exe to release that 500M memory space. Any idea?
Thx in advance!SQL Server releases memory only on OS request. Don't restart the service, if
any other application needs memory, it gets it. This is by design, because
SQL Server caches data and execution plans, so it can work faster.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!|||Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3 Gig
> in size. And the entire process costs 500M in memory usage. So every time
> after I run it, my system (as mentioned) just slows down to the point I have
> to restart the sqlserver.exe to release that 500M memory space. Any idea?
> Thx in advance!|||In addition to the other comments if you only have about 500MB I suggest you
set the MAX Memory setting in SQL Server to less than the max to try and
always leave some memory for the OS and other apps you run on your XP
machine. While SQL Server will release some memory when they demand it the
process can get a little painful on a workstation where lots of other things
are going on and there is little memory.
--
Andrew J. Kelly SQL MVP
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!
Wednesday, March 7, 2012
Memory Question
Hi,
Does SQL Server (Developer Edition, on XP Pro) ever release memory space
after processing a query? I have a query that runs against a database, 3 Gig
in size. And the entire process costs 500M in memory usage. So every time
after I run it, my system (as mentioned) just slows down to the point I have
to restart the sqlserver.exe to release that 500M memory space. Any idea?
Thx in advance!
SQL Server releases memory only on OS request. Don't restart the service, if
any other application needs memory, it gets it. This is by design, because
SQL Server caches data and execution plans, so it can work faster.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!
|||Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/default...;en-us;q321363
http://www.mssqlserver.com/faq/troub...memoryleak.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3 Gig
> in size. And the entire process costs 500M in memory usage. So every time
> after I run it, my system (as mentioned) just slows down to the point I have
> to restart the sqlserver.exe to release that 500M memory space. Any idea?
> Thx in advance!
|||In addition to the other comments if you only have about 500MB I suggest you
set the MAX Memory setting in SQL Server to less than the max to try and
always leave some memory for the OS and other apps you run on your XP
machine. While SQL Server will release some memory when they demand it the
process can get a little painful on a workstation where lots of other things
are going on and there is little memory.
Andrew J. Kelly SQL MVP
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!
Does SQL Server (Developer Edition, on XP Pro) ever release memory space
after processing a query? I have a query that runs against a database, 3 Gig
in size. And the entire process costs 500M in memory usage. So every time
after I run it, my system (as mentioned) just slows down to the point I have
to restart the sqlserver.exe to release that 500M memory space. Any idea?
Thx in advance!
SQL Server releases memory only on OS request. Don't restart the service, if
any other application needs memory, it gets it. This is by design, because
SQL Server caches data and execution plans, so it can work faster.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!
|||Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/default...;en-us;q321363
http://www.mssqlserver.com/faq/troub...memoryleak.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3 Gig
> in size. And the entire process costs 500M in memory usage. So every time
> after I run it, my system (as mentioned) just slows down to the point I have
> to restart the sqlserver.exe to release that 500M memory space. Any idea?
> Thx in advance!
|||In addition to the other comments if you only have about 500MB I suggest you
set the MAX Memory setting in SQL Server to less than the max to try and
always leave some memory for the OS and other apps you run on your XP
machine. While SQL Server will release some memory when they demand it the
process can get a little painful on a workstation where lots of other things
are going on and there is little memory.
Andrew J. Kelly SQL MVP
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!
Memory Question
Hi,
Does SQL Server (Developer Edition, on XP Pro) ever release memory space
after processing a query? I have a query that runs against a database, 3 Gi
g
in size. And the entire process costs 500M in memory usage. So every time
after I run it, my system (as mentioned) just slows down to the point I have
to restart the sqlserver.exe to release that 500M memory space. Any idea?
Thx in advance!SQL Server releases memory only on OS request. Don't restart the service, if
any other application needs memory, it gets it. This is by design, because
SQL Server caches data and execution plans, so it can work faster.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!|||Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/defaul...b;en-us;q321363
http://www.mssqlserver.com/faq/trou...-memoryleak.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every tim
e
> after I run it, my system (as mentioned) just slows down to the point I ha
ve
> to restart the sqlserver.exe to release that 500M memory space. Any idea?
'
> Thx in advance!|||In addition to the other comments if you only have about 500MB I suggest you
set the MAX Memory setting in SQL Server to less than the max to try and
always leave some memory for the OS and other apps you run on your XP
machine. While SQL Server will release some memory when they demand it the
process can get a little painful on a workstation where lots of other things
are going on and there is little memory.
Andrew J. Kelly SQL MVP
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!
Does SQL Server (Developer Edition, on XP Pro) ever release memory space
after processing a query? I have a query that runs against a database, 3 Gi
g
in size. And the entire process costs 500M in memory usage. So every time
after I run it, my system (as mentioned) just slows down to the point I have
to restart the sqlserver.exe to release that 500M memory space. Any idea?
Thx in advance!SQL Server releases memory only on OS request. Don't restart the service, if
any other application needs memory, it gets it. This is by design, because
SQL Server caches data and execution plans, so it can work faster.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!|||Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/defaul...b;en-us;q321363
http://www.mssqlserver.com/faq/trou...-memoryleak.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every tim
e
> after I run it, my system (as mentioned) just slows down to the point I ha
ve
> to restart the sqlserver.exe to release that 500M memory space. Any idea?
'
> Thx in advance!|||In addition to the other comments if you only have about 500MB I suggest you
set the MAX Memory setting in SQL Server to less than the max to try and
always leave some memory for the OS and other apps you run on your XP
machine. While SQL Server will release some memory when they demand it the
process can get a little painful on a workstation where lots of other things
are going on and there is little memory.
Andrew J. Kelly SQL MVP
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!
Subscribe to:
Comments (Atom)