Parallelism problem?

Hi All,
My environment configuration is:
* 4 processor Xeon 2000 Intell Machine with hyperthreading on (OS sees 8
virtual processors) with 4GB memory
* Win 2K advanced
* SQL 2K Enterprise (sp3a)
This is the problem I experience. Consider this query on a table
(StatisticsConsultation) containing 1 mln records.
SELECT COUNT(*) FROM Clinic
join StatisticsConsultation on StatisticsConsultation.ClinicId = Clinic.ClinicId
join vwDoctor D on StatisticsConsultation.DoctorId = D.DoctorId
join Specialty on StatisticsConsultation.SpecialtyId = Specialty.SpecialtyId
join vwPerson P on StatisticsConsultation.PersonId = P.PersonId where
Clinic.RegionId = 345
the result of the query is the number about 800.000.
When the server is allocated only for that query it takes 4 seconds
(parallelism used)
When there is a background process which constantly uses only 1 processor
out of 8
(other resources are not highly utilised) the time rises up to 25 seconds.
1 .When I specify MAXDOP 1 option the query takes 7 seconds with the
backgroud process.
Can anyone explain me why this happens? Is it parallelism bag?
2. The above query when revriten
SELECT COUNT(*) FROM Clinic
join StatisticsConsultation on StatisticsConsultation.ClinicId = Clinic.ClinicId where Clinic.RegionId = 345
takes only 1 sec in comparison to 4 seconds. Why SQL Server doesn't optimize
it correctly?
Thank you in advance,
Regards,
Tomaszparallelism is a very operation to optimize for all cases.
this is one area where the SQL Server default setting
needs to be investigated.
I would test the query with MAXDOP 1, 2 & 4
most definitely, not allow use of more than the physical
processors.
I think you will find 1 or 2 is preferred when taking into
account overall response times for multiple users, and 4
for off hours maintainence
the last query is different, the extra joins could affect
row count
>--Original Message--
>Hi All,
>My environment configuration is:
>* 4 processor Xeon 2000 Intell Machine with
hyperthreading on (OS sees 8
>virtual processors) with 4GB memory
>* Win 2K advanced
>* SQL 2K Enterprise (sp3a)
>This is the problem I experience. Consider this query on
a table
>(StatisticsConsultation) containing 1 mln records.
>SELECT COUNT(*) FROM Clinic
> join StatisticsConsultation on
StatisticsConsultation.ClinicId =>Clinic.ClinicId
> join vwDoctor D on StatisticsConsultation.DoctorId =D.DoctorId
> join Specialty on StatisticsConsultation.SpecialtyId =>Specialty.SpecialtyId
> join vwPerson P on StatisticsConsultation.PersonId =P.PersonId where
>Clinic.RegionId = 345
>the result of the query is the number about 800.000.
>When the server is allocated only for that query it takes
4 seconds
>(parallelism used)
>When there is a background process which constantly uses
only 1 processor
>out of 8
>(other resources are not highly utilised) the time rises
up to 25 seconds.
>1 .When I specify MAXDOP 1 option the query takes 7
seconds with the
>backgroud process.
>Can anyone explain me why this happens? Is it parallelism
bag?
>2. The above query when revriten
>SELECT COUNT(*) FROM Clinic
> join StatisticsConsultation on
StatisticsConsultation.ClinicId =>Clinic.ClinicId where Clinic.RegionId = 345
>takes only 1 sec in comparison to 4 seconds. Why SQL
Server doesn't optimize
>it correctly?
>Thank you in advance,
>Regards,
>Tomasz
>
>.
>

Paging file size for server with 128GB memory

Hi,

Anyone knows what the size should the Paging File be for a 8 P 16 Core Opteron server with 128GB memory installed? The server will be running SQL 05 Ent and IIS, on top of Win2k3 R2 x64 Ent.

Please also give the reason for that size.

Regards,

dong

On a SQL Server you want the page file size to be as small as possible so that SQL Server will be using real memory instead of slower virtual memory. SQL Servers are resource-rich centralized beasts. That's one of the reasons they are faster than a desktop database or btrieve library. The optimal size would be 0 but you need a pagefile big enough to hold any memory dumps your server might create. If you can handle just getting mini-dumps which are just 64K, you could set your pagefile very very small. Otherwise, you need a pagefile at least the same size as your memory size, which sucks. Its been back to SQL 4.2 since I actually used a memory dump, so I generally turn them off. You can always turn them back on if your machine starts acting screwey.

Paging File size

I hope these are pretty basic SQL Server management questions. I'm running a SP that uses a lot of memory. The server has about 8GB and it uses 7.95GB. When the memory usage goes up, so does the size of the paging file. I'm assuming the page file grows so it can write the memory contents to disk in the case of a crash. How necessary is this? It seems like any advantage I'd gain from working out of memory is lost because I'm working off the drive too.

Can I/should I turn this "feature" off?

Thanks!

Brian

Paging file is used to swap files quickly in and out of RAM. The system can't possibly keep everything in RAM that it ever needs. So it swaps in use files back and forth between RAM and the pagefile as needed. It's a secondary backup to RAM, sort of.

The paging file is growing because if SQL Server is using 7.95 GB of RAM of 8 available, there's not enough RAM left for the operating system to function, and it is forced to rely on the paging file for its normal operations.

A general rule of thumb for a SQL Server is that the paging file should be at least double the amount of RAM. So if you have 8 GB of RAM, the paging file should be 16 GB ... a preferably on a different disk than any of the SQL files. Set the paging file to a hard size, don't let Windows manage your paging file.

If your server has 4 or more GB of RAM, you should reserve at least 1 GB of RAM for the OS. You do this by setting the maximum amount of memory that SQL Server can use to the total amount - 1 GB. So for 8GB of RAM, set SQL to use a maximum of 7 GB.

If 7.95 GB of RAM are being used though not necessarily by SQL Server, the OS may be forcing SQL Server to give up memory and forcing it to page. Make sure that SQL Server has been granted "Lock pages in memory" rights in the group policy editor.

|||

Robert,

The thing that bothers me is the files grow simultaneously. They both start out below 1GB and both grow at approximately the same rate. That is why I wondered if Windows was preparing for a memory dump by keeping an "Image" of memory in case of a crash. I haven't used this setting since XP, but assume it's still around in 2003. Although I think it's disabled by default.

Whatever the case, I'll assume that the behavior is normal for now. I have implemented a couple of your suggestions.

Thanks for the help.

Brian

pageiolatch_sh and latch_ex

Would the above waittypes strongly correlate to IO ? Or could it also me
memory ?
I thought I had seen more info of these types out here
http://sqldev.net/articles/wait_types.htm, but Gert is working on it I guess
at this moment and the wait types that I was interested in have just what
that waitype means and doesnt have more details.
Does anyone know where I can find more about those waits ?See http://support.microsoft.com/default.aspx?scid=kb;en-us;822101.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23H2qANFcFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Would the above waittypes strongly correlate to IO ? Or could it also me
> memory ?
> I thought I had seen more info of these types out here
> http://sqldev.net/articles/wait_types.htm, but Gert is working on it I
> guess
> at this moment and the wait types that I was interested in have just what
> that waitype means and doesnt have more details.
> Does anyone know where I can find more about those waits ?
>

pagefile and memory advice?

Hello, I've been reading about this topic, and I've gotten myself more
confused, not less.

We have a single-processor license SQL Server Standard 2005 (xeon 2.8
ghz) with 4 GB RAM in Windows Server 2003 SP1 Standard.

I turned on the /3GB switch in boot.ini but not PAE or AWE, would it be
good to have either one??

Quote:

Originally Posted by

>From what I have seen so far, these are advanced settings for bigger

servers or databases than we presently have.

The SQL server is for Great Plains accounting, the largest db is around
3 GB now, and probably grows a GB per year.

We have Abra Suite running as well, someday this will get changed over
to a SQL database, it is FoxPro for now.

I know not to have IIS or other apps running on this box, other than a
couple Access files that are accessed by client apps.

We expect to make more use of this server in the coming years, to
contain intranet database files and web app files and maybe even
SharePoint, maybe someday we need a bigger server..

I was advised to remove or reduce the pagefile size, how should I
determine how big the pagefile should be?? I am thinking 1 GB down from
4 GB.

Thank you for reading this, TomOn 19.12.2006 15:48, tlyczko wrote:

Quote:

Originally Posted by

Hello, I've been reading about this topic, and I've gotten myself more
confused, not less.
>
We have a single-processor license SQL Server Standard 2005 (xeon 2.8
ghz) with 4 GB RAM in Windows Server 2003 SP1 Standard.
>
I turned on the /3GB switch in boot.ini but not PAE or AWE, would it be
good to have either one??
>

Quote:

Originally Posted by

>>From what I have seen so far, these are advanced settings for bigger

servers or databases than we presently have.
>
The SQL server is for Great Plains accounting, the largest db is around
3 GB now, and probably grows a GB per year.
>
We have Abra Suite running as well, someday this will get changed over
to a SQL database, it is FoxPro for now.
>
I know not to have IIS or other apps running on this box, other than a
couple Access files that are accessed by client apps.
>
We expect to make more use of this server in the coming years, to
contain intranet database files and web app files and maybe even
SharePoint, maybe someday we need a bigger server..
>
I was advised to remove or reduce the pagefile size, how should I
determine how big the pagefile should be?? I am thinking 1 GB down from
4 GB.

That's not exactly a MS SQL Server question. Generally you should set
max memory *in* SQL Server to be not more than physically available.
Other than that I have no advice to offer.

Regards

robert|||In my experience with ~5GB Great Plains, 2GB memory is more than enough. I
would recommend against sharing an accounting server with any other
non-accounting applications for security reasons.

In advising on pagefile size we would need your disk layout, RAID level,
etc.

I prefer multiple fixed size, unfragmented, paging files spread across all
disk sets except the ones containing the transaction logs and tempdb. I also
size my server so that the paging file is rarely, if ever, used.

The pagefile is by default sized to do a memory dump if the server fails. I
have never met anyone who has actually used this memory dump for server post
mortem, but I'm sure someone has... However, if space is so short that 3GB
would matter then you likely need to get more and/or bigger disks. I prefer

Quote:

Originally Posted by

50% free space on all disk sets.

"tlyczko" <tlyczko@.gmail.comwrote in message
news:1166539739.038505.10810@.80g2000cwy.googlegrou ps.com...

Quote:

Originally Posted by

Hello, I've been reading about this topic, and I've gotten myself more
confused, not less.
>
We have a single-processor license SQL Server Standard 2005 (xeon 2.8
ghz) with 4 GB RAM in Windows Server 2003 SP1 Standard.
>
I turned on the /3GB switch in boot.ini but not PAE or AWE, would it be
good to have either one??
>

Quote:

Originally Posted by

>>From what I have seen so far, these are advanced settings for bigger

servers or databases than we presently have.
>
The SQL server is for Great Plains accounting, the largest db is around
3 GB now, and probably grows a GB per year.
>
We have Abra Suite running as well, someday this will get changed over
to a SQL database, it is FoxPro for now.
>
I know not to have IIS or other apps running on this box, other than a
couple Access files that are accessed by client apps.
>
We expect to make more use of this server in the coming years, to
contain intranet database files and web app files and maybe even
SharePoint, maybe someday we need a bigger server..
>
I was advised to remove or reduce the pagefile size, how should I
determine how big the pagefile should be?? I am thinking 1 GB down from
4 GB.
>
Thank you for reading this, Tom
>

|||Russ Rose wrote:

Quote:

Originally Posted by

In my experience with ~5GB Great Plains, 2GB memory is more than enough. I
would recommend against sharing an accounting server with any other
non-accounting applications for security reasons.
>
In advising on pagefile size we would need your disk layout, RAID level,
etc.
>
I prefer multiple fixed size, unfragmented, paging files spread across all
disk sets except the ones containing the transaction logs and tempdb. I also
size my server so that the paging file is rarely, if ever, used.
>
The pagefile is by default sized to do a memory dump if the server fails. I
have never met anyone who has actually used this memory dump for server post
mortem, but I'm sure someone has... However, if space is so short that 3GB
would matter then you likely need to get more and/or bigger disks. I prefer

Quote:

Originally Posted by

50% free space on all disk sets.

Hello, thank you for replying.

Disk space is not critical per se, I am inquiring about pagefile size
etc., SQL is doing well at managing itself for us, because someone in
another NG suggested I should eliminate or reduce the pagefile, it's
currently 4 GB when the memory has been increased to 4GB.

Thanks, Tom