distributed transaction: helloi

Showing posts with label helloi. Show all posts

Thursday, March 29, 2012

distribute the part of data to different database on Same server

Hello
I have a very general question on SQL memory management. We have a SQL 2000
(64 bit) on Windows 2003 server. Our production database is growing in no
time. And we do see lot of contention on the Temp db on load. We are planning
to distribute the part of data to different database on the same server. By
doing this will there be any improvement in contention & performance? Is it
advisable.
By doing this we will be adding the overhead on different procedure writing
cross database queries.
Your help will be appreciated
Thanks
Naveen
The one file per CPU is recommended in 2000, and there is also a trace flag
that -might- help (if the situation is correct). See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
Agreed, though, this is probably a situation where pretty straightforward
optimization will do the trick.
The OP mentioned that it was during a data load, so I'm curious as to how
the data is being loaded? Are bulk loads being done, and if so, what is the
batch size being used? Larger batch sizes will buffer to tempdb. The
general recommendation I've seen is to shoot for batch sizes of around 10000
rows to avoid problems.

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Will Alber" <junk@.crazy-pug.co.uk> wrote in message
news:%23BSljDBuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> Moving data into another database on the same server will not help tempdb
> at all, unless the 'other database' is in a separate instance of SQL
> Server - and then you'll still get IO contention unless you ensure that
> tempdb for the new instance is on a separate set of disks.
> My first approach would have to be identifying what is causing the tempdb
> contention - it might be one or two major queries, or widespread
> throughout the DB. If the former, well, optimise - if the latter, either
> rearchitect or split the load between different instances, or even better,
> different boxes.
> Does tempdb comprise multiple files? You should have one file per CPU, if
> I remember correctly - at least, for MSSQL 2005 this is recommended, and
> it can't hurt 2000...
> "Naveen" <Naveen@.discussions.microsoft.com> wrote in message
> news:DB650185-CA27-4AFA-8513-B7E91529D4C9@.microsoft.com...
>
|||Thanks for your input.
We did all the optimization on the queries. We don’t load huge data on the
application but number of transaction are huge….(close to 200 call per sec )
Now ..reason for moving the data to different database on the same server is
because of the load we expecting on the system in upcoming releases. The
data we are moving to different database is the area where we are expecting
the load and we are bringing in couple of thousand more users. The CPU is
around 65 % and we do see the spike to 80 % during the month end and temp DB
contention goes up as well.
If new database on to different disks will that any way to reduce the Server
load…We don’t want to move to different server because the application is so
tightly integrated we defiantly need to have cross queries.
"Adam Machanic" wrote:

> The one file per CPU is recommended in 2000, and there is also a trace flag
> that -might- help (if the situation is correct). See:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
> Agreed, though, this is probably a situation where pretty straightforward
> optimization will do the trick.
> The OP mentioned that it was during a data load, so I'm curious as to how
> the data is being loaded? Are bulk loads being done, and if so, what is the
> batch size being used? Larger batch sizes will buffer to tempdb. The
> general recommendation I've seen is to shoot for batch sizes of around 10000
> rows to avoid problems.
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Will Alber" <junk@.crazy-pug.co.uk> wrote in message
> news:%23BSljDBuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>
|||What kind of device do you have tempdb on? How big is it? There are lots
of options for optimizing it... More spindles, a RamSan, or even a RAM disk
(if you can afford losing the memory to tempdb)...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:BFF130E0-463C-4F3F-9F21-63581066FB12@.microsoft.com...[vbcol=seagreen]
> Thanks for your input.
> We did all the optimization on the queries. We don’t load huge data on
> the
> application but number of transaction are huge….(close to 200 call per
> sec )
> Now ..reason for moving the data to different database on the same server
> is
> because of the load we expecting on the system in upcoming releases. The
> data we are moving to different database is the area where we are
> expecting
> the load and we are bringing in couple of thousand more users. The CPU is
> around 65 % and we do see the spike to 80 % during the month end and temp
> DB
> contention goes up as well.
> If new database on to different disks will that any way to reduce the
> Server
> load…We don’t want to move to different server because the application is
> so
> tightly integrated we defiantly need to have cross queries.
>
> "Adam Machanic" wrote:
|||Huu...I need to verify this information with the Server Administration
team.Will get back to you as Soon as I have the ifnormation
"Adam Machanic" wrote:

> What kind of device do you have tempdb on? How big is it? There are lots
> of options for optimizing it... More spindles, a RamSan, or even a RAM disk
> (if you can afford losing the memory to tempdb)...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Naveen" <Naveen@.discussions.microsoft.com> wrote in message
> news:BFF130E0-463C-4F3F-9F21-63581066FB12@.microsoft.com...
>

Tuesday, March 27, 2012

distribute the part of data to different database on Same server

Sunday, March 25, 2012

Distinct problem

Hello!
I need some help with this DISTINCT problem. What am I doing wrong?
My T-SQL is lousy, I know...
SELECT DISTINCT (Deliver) FROM (SELECT Deliver=CASE
WHEN DelivDate >'2006-12-31' THEN '1999-12-31'
ELSE DelivDate
END
FROM TTransaction)
Result:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.
Regards
ClarkieTry this
SELECT DISTINCT (Deliver) FROM (SELECT Deliver=CASE
WHEN DelivDate >'2006-12-31' THEN '1999-12-31'
ELSE DelivDate
END
FROM TTransaction) Deliver|||Works like a charm!
Tack s mycket!
//Clarkie
"Patrik" <corneliusson@.gmail.com> wrote in message
news:1138615960.416301.25580@.o13g2000cwo.googlegroups.com...
> Try this
> SELECT DISTINCT (Deliver) FROM (SELECT Deliver=CASE
> WHEN DelivDate >'2006-12-31' THEN '1999-12-31'
> ELSE DelivDate
> END
> FROM TTransaction) Deliver
>

Friday, March 9, 2012

Displaying Failed job in SQL Server 2005 Management Studio

Hello!
I have noticed that failed jobs in SQL Server 2005 Management Studio
have the same icon as succeeded. This makes difficult visually identifying
ones requiring immediate attention. Am I missing something? Is there a way
to assign different icon to failed jobs? Currently I have to inspect each
job individually. Of course, I can write a script to identify failed jobs
but having different icon for failed jobs (for example as it is currently in
EM) would be nice.
Thanks,
IgorAre you viewing the jobs in the Job Activity Monitor? That's
where you would want to look, not the jobs listed in the
Jobs node in Object Explorer. Object Explorer just shows the
job objects.
-Sue
On Wed, 25 Jan 2006 15:54:54 -0800, "imarchenko"
<igormarchenko@.hotmail.com> wrote:

>Hello!
> I have noticed that failed jobs in SQL Server 2005 Management Studio
>have the same icon as succeeded. This makes difficult visually identifying
>ones requiring immediate attention. Am I missing something? Is there a way
>to assign different icon to failed jobs? Currently I have to inspect each
>job individually. Of course, I can write a script to identify failed jobs
>but having different icon for failed jobs (for example as it is currently i
n
>EM) would be nice.
>Thanks,
>Igor
>|||Thanks Sue!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:2mbgt15p5732dr09kvko0p1mnctqbfharr@.
4ax.com...
> Are you viewing the jobs in the Job Activity Monitor? That's
> where you would want to look, not the jobs listed in the
> Jobs node in Object Explorer. Object Explorer just shows the
> job objects.
> -Sue
> On Wed, 25 Jan 2006 15:54:54 -0800, "imarchenko"
> <igormarchenko@.hotmail.com> wrote:
>
>

Displaying Failed job in SQL Server 2005 Management Studio

Hello!
I have noticed that failed jobs in SQL Server 2005 Management Studio
have the same icon as succeeded. This makes difficult visually identifying
ones requiring immediate attention. Am I missing something? Is there a way
to assign different icon to failed jobs? Currently I have to inspect each
job individually. Of course, I can write a script to identify failed jobs
but having different icon for failed jobs (for example as it is currently in
EM) would be nice.
Thanks,
Igor
Are you viewing the jobs in the Job Activity Monitor? That's
where you would want to look, not the jobs listed in the
Jobs node in Object Explorer. Object Explorer just shows the
job objects.
-Sue
On Wed, 25 Jan 2006 15:54:54 -0800, "imarchenko"
<igormarchenko@.hotmail.com> wrote:

>Hello!
> I have noticed that failed jobs in SQL Server 2005 Management Studio
>have the same icon as succeeded. This makes difficult visually identifying
>ones requiring immediate attention. Am I missing something? Is there a way
>to assign different icon to failed jobs? Currently I have to inspect each
>job individually. Of course, I can write a script to identify failed jobs
>but having different icon for failed jobs (for example as it is currently in
>EM) would be nice.
>Thanks,
>Igor
>
|||Thanks Sue!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:2mbgt15p5732dr09kvko0p1mnctqbfharr@.4ax.com...
> Are you viewing the jobs in the Job Activity Monitor? That's
> where you would want to look, not the jobs listed in the
> Jobs node in Object Explorer. Object Explorer just shows the
> job objects.
> -Sue
> On Wed, 25 Jan 2006 15:54:54 -0800, "imarchenko"
> <igormarchenko@.hotmail.com> wrote:
>

Friday, February 24, 2012

Display result in hours, weeks and month

Hello!
I have data stored in a fld which is a datetime difference in hours. I
need to display it in 'Hours' (if it is less than 24), 'W

s' (if it is
greater than 168) and in 'Month' (if it is greater than 720). Is there
an easy to do it?
Thanks for your help!
*** Sent via Developersdex http://www.examnotes.net ***You forgot to mention dates
take a look at this, you might want to include years also
create table #timeStuff (TimeField int)
insert into #timeStuff
select 5 union all
select 55 union all
select 125 union all
select 1225 union all
select 555 union all
select 721 union all
select 719
select TimeField,case
when TimeField < 24 then 'hours'
when TimeField between 24 and 168 then 'days'
when TimeField between 169 and 719 then 'w

s'
when TimeField > 720 then 'months'
end
from #timeStuff
drop table #timeStuff
http://sqlservercode.blogspot.com/|||Would this work?
select TimeField,case
when TimeField < 24 then TimeField * 1.0
when TimeField between 24 and 168 then Timefield/24.0
when TimeField between 169 and 719 then Timefield/(24.0*7.0)
when TimeField > 720 then Timefield/(24.0*30.0)
end as result
from #timeStuff
(using DDL from second message:
create table #timeStuff (TimeField int)
insert into #timeStuff
select 5 union all
select 55 union all
select 125 union all
select 1225 union all
select 555 union all
select 721 union all
select 719
The issue here is that you are storing an interval, not hard dates, but
you want to figure out the number of months in hours. Since some
months are 30 days and some are 31 days (february-28 days), you won't
get an exact result here.
The other calculations should work since there are always 24 hours in a
day and 7 days in a w

.
Christian|||No. This is what I want based on your data.
5 = 5 hurs
55 = 2 days 7 hrs
125 = 5 days 2 hrs
1225 = 1 month w

s and hrs
555 = 3 w

s and hours
and so on ...
*** Sent via Developersdex http://www.examnotes.net ***|||drop table #timeStuff
go
create table #timeStuff (hours int)
insert into #timeStuff
select 5 union all
select 55 union all
select 125 union all
select 1225 union all
select 555 union all
select 721 union all
select 719
go
this should give you what you want, though I don't know how you want to
calculate months since that is a variable amount of time (w

s are exactly
7 days (well, except for leap years, but for all intents and purposes :))
select hours as totalHours, hours/168 as w

s, (hours % 168) / 24 as days,
((hours % 168) % 24) as hours
from #timestuff
/*
totalHours w

s days hours
-- -- -- --
5 0 0 5
55 0 2 7
125 0 5 5
1225 7 2 1
555 3 2 3
721 4 2 1
719 4 1 23
*/
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OlHMYqKOGHA.2036@.TK2MSFTNGP14.phx.gbl...
> No. This is what I want based on your data.
>
> 5 = 5 hurs
> 55 = 2 days 7 hrs
> 125 = 5 days 2 hrs
> 1225 = 1 month w

s and hrs
> 555 = 3 w

s and hours
> and so on ...
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks a lot everyone!!!!
*** Sent via Developersdex http://www.examnotes.net ***

Tuesday, February 14, 2012

DISPLAY DAtes

Hello

I have this as a calculated field for displaying dates like 1/1/2007

=DATEADD("d", Fields!TimeIncInteger.Value , DATEADD("n", Parameters!GMTOffSet.Value,Parameters!BDateTime.Value))

This format increments the day vaues fine but how can I get it to increment month vaues too.

i.e presently it gives me day numbers perfect 1/2/2007 ..2nd january

but for 5th february it gives date as 1/5/2007. month value remains 1.

any help appreciated.

thanks

I haven't tried this, but it looks like you are adding the minutes instead of months. Change the 'n' to an 'm'.

=DATEADD("d", Fields!TimeIncInteger.Value , DATEADD("m", Parameters!GMTOffSet.Value,Parameters!BDateTime.Value))

Hope this helps.

Jarret

|||

Thanks Jarret,

I do need to add the mkinute offset too..its the conversion from UTC to local time.

for the month I have done this now

=DATEADD("m",Parameters!MONTH.value,DATEADD("d", Fields!TimeIncInteger.Value , DATEADD("n", Parameters!GMTOffSet.Value,Parameters!BDateTime.Value)))

but this gives me an incremented month..

ie for Janury 5th..

it gives the date as 2/5/2007 i.e feb 5th

Thanks

|||

Oh, I'm sorry. Can you send some example records for TimeIncInteger, GMTOffSet, BDateTime, and how you want them to display as well?

Jarret

|||

All right,

The inc integer values go from 0-27 for february,

GMT offset is fixed at -360

and BDate Time is in the format Feb 1 2007 6:00AM

I think if I do DATEADD("m",Parameters!GMTOffSet.Value - 1, DATEADD("d", Fields!TimeIncInteger.Value , DATEADD("n", Parameters!GMTOffSet.Value, Parameters!BDateTime.Value)))

it would work but would it be best practice to put -1 there.

thanks

kiran

|||

Your original formula subtracts 6 hours from your BDateTime value, then adds TimeIncInteger days to that.

For example, the BDateTime value is 2/15/07 6am and the TimeIncInteger value is 21. The first thing in your expression is to remove 6 hours, making it 2/15/07 12am. Then, you add 21 days to this, making it 3/8/07 12am.

Depending on your starting day and increment value, the month will change. Is this not what you wanted?

Jarret

|||

Yes Jarret,

The day should change like that.

Waht I have in the report is dates by month where month is a parameter.

SO on selection of a month the day values should go from 1...end of month which happens by the time increment value.

The problem I was facing is since the parameter is a dropdown of month how to get the month value to change.

After adding =DATEADD("m",Parameters!MONTH.value-1,DATEADD("d"......................)

this works but I wanted to know of a better way to do so rather than using the -1.

Thanks for the help.

Kiran.

Display database column in alphabetical order

Hello

I know how I can display a list of names in alphebetical order on my website:

Select L as [Last Name]

From Name_CatE
Where Education = 'yes'
Order By
LName ASC

However, to make things a little more orginised I would like to view my database table column in alphabetical order also, but ithie code does not work within my database.

What do I need to change in the following code, to view my database table column in a-z order?

SELECT LName FROM Name_CatE
ORDER BY LName ASC

Thanks

Lynn

Are you asking how to physically sort the rows in the table in LName order? If so, you could use a clustered index. But typically this is not a good idea for fields like LName, which could have many duplicate values. Check Books Online for details about the implications of doing this, particularly on performance.

But do you really need this? Is it just a preference, or do you have a solid business requirement for this? If it's just a preference, don't do it. that's what the ORDER BY clause is for.

Don

Thursday, March 29, 2012

distribute the part of data to different database on Same server

Tuesday, March 27, 2012

distribute the part of data to different database on Same server

Sunday, March 25, 2012

Distinct problem

Friday, March 9, 2012

Displaying Failed job in SQL Server 2005 Management Studio

Displaying Failed job in SQL Server 2005 Management Studio

Friday, February 24, 2012

Display result in hours, weeks and month

Tuesday, February 14, 2012

DISPLAY DAtes

Display database column in alphabetical order

distributed transaction

Blog Archive

About Me