Hey all,
Is there any way that I can find the current size limitation (eg, MSDE = 2gb
limit) of a database from a db query / DMO dll call etc?
ThanksYou can get the edition and product information with these:
SELECT SERVERPROPERTY('productversion') AS [ProductVersion]
SELECT SERVERPROPERTY('ProductLevel') AS [ProductLevel]
SELECT SERVERPROPERTY('Edition') AS [Edition]
But there is no bit that I know of that you can see for the size limitation.
It's pretty simple though if it is MSDE it is 2GB, if it is SQLExpress it is
4GB otherwise there is no limit.
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:9F961CBD-4303-49DA-A8D8-8E2C63296BB5@.microsoft.com...
> Hey all,
> Is there any way that I can find the current size limitation (eg, MSDE =
> 2gb
> limit) of a database from a db query / DMO dll call etc?
> Thanks|||Thanks Andrew, but which bit out of the server results would show what
version the DB is running on?
I understand which DB's have limits, but I cant establish a way to find out
which type of DB system is being used by our customers in order to impose a
limit check.
Any help would be great! Thanks!
"Andrew J. Kelly" wrote:
> You can get the edition and product information with these:
>
> SELECT SERVERPROPERTY('productversion') AS [ProductVersion]
> SELECT SERVERPROPERTY('ProductLevel') AS [ProductLevel]
> SELECT SERVERPROPERTY('Edition') AS [Edition]
> But there is no bit that I know of that you can see for the size limitatio
n.
> It's pretty simple though if it is MSDE it is 2GB, if it is SQLExpress it
is
> 4GB otherwise there is no limit.
> --
> Andrew J. Kelly SQL MVP
>
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:9F961CBD-4303-49DA-A8D8-8E2C63296BB5@.microsoft.com...
>
>|||I am not sure i am understanding you properly. Are you asking how do you
determine if they are running MSDE or not? Is so then just run
SERVERPROPERTY('Edition') to see. You can check BooksOnLine under this
command for the proper results.
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:44A7095B-F895-49DD-AA34-28FFE7F4DC1E@.microsoft.com...
> Thanks Andrew, but which bit out of the server results would show what
> version the DB is running on?
> I understand which DB's have limits, but I cant establish a way to find
> out
> which type of DB system is being used by our customers in order to impose
> a
> limit check.
> Any help would be great! Thanks!
> "Andrew J. Kelly" wrote:
>|||Yeah, thats what im after.
When trying it out, ive got an MSDE installation locally and a full SQL 2000
on a server, but both return 'Developer Edition' from the 'Edition' server
property, so I assumed that this wasn't specific enough?
"Andrew J. Kelly" wrote:
> I am not sure i am understanding you properly. Are you asking how do you
> determine if they are running MSDE or not? Is so then just run
> SERVERPROPERTY('Edition') to see. You can check BooksOnLine under this
> command for the proper results.
>
> --
> Andrew J. Kelly SQL MVP
>
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:44A7095B-F895-49DD-AA34-28FFE7F4DC1E@.microsoft.com...
>
>|||Hmmm. I can see the Full version showing Developer if that what it is but I
would expect MSDE to show DeskTop. Are you sure you were pointing to the
correct instance? What does "engine edition" show?
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:B68E0408-2DE2-4CEA-886A-00338CBA8645@.microsoft.com...
> Yeah, thats what im after.
> When trying it out, ive got an MSDE installation locally and a full SQL
> 2000
> on a server, but both return 'Developer Edition' from the 'Edition' server
> property, so I assumed that this wasn't specific enough?
> "Andrew J. Kelly" wrote:
>|||Ok.. im a dumbass! (Looking at the wrong DB!)
Now my 'Engine Edition' Shows 1 (Desktop Edition) which is what I would
expect.
Do we know if the 'SERVERPROPERTY(EngineEdition)' returns similar results
for SQL 2005 & SQLExpress?
"Andrew J. Kelly" wrote:
> Hmmm. I can see the Full version showing Developer if that what it is but
I
> would expect MSDE to show DeskTop. Are you sure you were pointing to the
> correct instance? What does "engine edition" show?
> --
> Andrew J. Kelly SQL MVP
>
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:B68E0408-2DE2-4CEA-886A-00338CBA8645@.microsoft.com...
>
>|||These are the possible results from both the Edition and Engine Edition
properties in 2005 BOL:
'Desktop Engine'
'Developer Edition'
'Enterprise Edition'
'Enterprise Evaluation Edition'
'Personal Edition'
'Standard Edition'
'Express Edition'
'Workgroup Edition'
'Windows Embedded SQL'
---
Database Engine edition of the instance of SQL Server installed on the
server.
1 = Personal or Desktop Engine
2 = Standard
3 = Enterprise (This is returned for Enterprise, Enterprise Evaluation, and
Developer.)
4 = Express
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:24868DAD-04FA-42B2-BF4E-7571BF6DCB06@.microsoft.com...
> Ok.. im a dumbass! (Looking at the wrong DB!)
> Now my 'Engine Edition' Shows 1 (Desktop Edition) which is what I would
> expect.
> Do we know if the 'SERVERPROPERTY(EngineEdition)' returns similar results
> for SQL 2005 & SQLExpress?
> "Andrew J. Kelly" wrote:
>sql
Showing posts with label call. Show all posts
Showing posts with label call. Show all posts
Sunday, March 25, 2012
Friday, February 17, 2012
database performance management
Hi
I am using SQL server database with asp as front end. I use asp command object to call sql stored procedure. The procedure runs a while loop for say 100,000 records and based on the IF condition calls particular stored procs which process the record.
I am running this app on a p4 IBM pc with win 2000 sever and IIS on same m/c . The cpu utilisation goes upto 98-99% and the process has started running very slow of late. Is this slow processing speed a hardware/OS problem or is it due to calls for stored proc within stored proc? how can i optimise the process. Each stored proc called does have if conditions,table scans etc.
please adviseYou might post your SP for more help, might try recompiling it as well.|||I have attatched the main stored proc(fetchobktotmp_SP_March08.txt) and the proc which is called most often(sot_Sp_March08.txt.. please have a look
regds|||Both SQL & ISS on same machine will have resource crunch and performance would be affected, try to seperate them or add more physical memory to the box. And also assign more memory to SQL itself.
As suggested recompile the SP, update stats the involved tables etc etc.|||Having SQL Server as well as IIS on the same machine leads to a perrformance crunch but inorder to really identify what is the issue you would need to check, RAM & Processor speed. They play a big part.
It is important to identify if this is processor issue or RAM issue, and you would need to check through performance monitor what are the CPU cycle consumption patterns of the IIS and SQL Server.
At the SQL Server level itself, you can check using the trace files if there are any other issues. But i guess, these kind of performance testing has to be done over a period of time and then analyse the results.|||Contribution- http://www.sql-server-performance.com/q&a45.asp|||Hi
currently the machine has 512MB RAM.. we hve observed that as no of records in the table increases (say 600000 or so) thge performance degrades.. for starters we hve decided to hve a serer grade machine for SQL server and iis both.. may be we wud even seperate them ..|||I feel the server is stressed out due to both at one place and SQL is not able to exeucte queries due to less available resources, such as memory which will have major affect for any performance.
I am using SQL server database with asp as front end. I use asp command object to call sql stored procedure. The procedure runs a while loop for say 100,000 records and based on the IF condition calls particular stored procs which process the record.
I am running this app on a p4 IBM pc with win 2000 sever and IIS on same m/c . The cpu utilisation goes upto 98-99% and the process has started running very slow of late. Is this slow processing speed a hardware/OS problem or is it due to calls for stored proc within stored proc? how can i optimise the process. Each stored proc called does have if conditions,table scans etc.
please adviseYou might post your SP for more help, might try recompiling it as well.|||I have attatched the main stored proc(fetchobktotmp_SP_March08.txt) and the proc which is called most often(sot_Sp_March08.txt.. please have a look
regds|||Both SQL & ISS on same machine will have resource crunch and performance would be affected, try to seperate them or add more physical memory to the box. And also assign more memory to SQL itself.
As suggested recompile the SP, update stats the involved tables etc etc.|||Having SQL Server as well as IIS on the same machine leads to a perrformance crunch but inorder to really identify what is the issue you would need to check, RAM & Processor speed. They play a big part.
It is important to identify if this is processor issue or RAM issue, and you would need to check through performance monitor what are the CPU cycle consumption patterns of the IIS and SQL Server.
At the SQL Server level itself, you can check using the trace files if there are any other issues. But i guess, these kind of performance testing has to be done over a period of time and then analyse the results.|||Contribution- http://www.sql-server-performance.com/q&a45.asp|||Hi
currently the machine has 512MB RAM.. we hve observed that as no of records in the table increases (say 600000 or so) thge performance degrades.. for starters we hve decided to hve a serer grade machine for SQL server and iis both.. may be we wud even seperate them ..|||I feel the server is stressed out due to both at one place and SQL is not able to exeucte queries due to less available resources, such as memory which will have major affect for any performance.
Subscribe to:
Posts (Atom)