Standard Of Capturing Memory Utalization Per Database
Mar 30, 2007Hi all ,
Is there any way to get memory utilization for a Database.
Regards
Sufian
ADVERTISEMENT
AWE Memory On SQL 2005 Standard
Jun 8, 2007Is AWE enabled on SQL 2005 Standard? We currently have it setup using "locked pages in memory" for a domain account and haven't noticed any performance problems.
This article however states that only the SQL2005 Enterprise Edn supports "locked pages in memory". Can anyone confirm this?
http://support.microsoft.com/kb/918483
If I'm mistaken, how should the memory options be set for a server with 8gb ram running on SQL 2005 Standard which runs on Win2003 Enterprise? Should the min and max memory be set or left alone as default?
Max. Memory Supported By Standard Edition
Aug 1, 2001I learned that SQL2000 Standard Edition can only support up to 2GB RAM, we have a WIN2K system on a server with 4CPU, 4GB RAM,
if we run SQL2000 Stanard Edition on it, will the 2 extra 2GB of RAM be wasted ?
Since there is a big diff. between licensing cost of SQL Standard and Enterprise, that's why the user choose Std. edition.
Any opinion or comment...
Lock Pages In Memory On X64 Standard
May 18, 2007All,
I have a new database server running W2K3/MSSQL2K5 x64. I read in BOL that you can use the Lock Pages in Memory option to improve performance on your database server. I did some research on the internet and some sources are stating that you can only use that option with MSSQL2K5 Enterprise but in BOL they state that you can use this option in Standard/Enterprise.
Can anyone confirm that "Lock Pages in Memory on x64 Standard" works in MSSQL2K5 Standard?
Thanks in advance for the help.
Memory Limitations, 2005 Standard
Mar 9, 2006Hi all,I have recently implemented a SQL 2005 cluster using SQL 2005 Standardon Windows 2003 Enterprise edition.Both nodes have 4GB of RAM and according to the datasheet, SQL 2005Standard can support the OS maximum memory amount and Win2K3 EntEdition can support 64GB!However, in Enterprise Manager, if I go to the "Memory" tab of theinstance properties I can't increase the memory beyond 2147483647(which I assume is around 2GB).I don't have AWE enabled but am unsure as to the ramifications of this.Any advice anyone could provide would be greatly appreciated.Many thanks,Ian
View 2 Replies View RelatedMaximum Memory On Sql7 Standard Edition
Feb 20, 2002Hello,
I have been trying to find out what the maximum memory we can have on SQL7 standard edition server. Have not been able to find it anywhere...
Anuy ideas would be greately appreciated.
TIA
B.
SQL Server 2000 Standard Memory Flatline
Jun 26, 2007Hi all,
We have an SQL 2000 Standard SP4 system with 3gb of RAM running on Windows 2003 Server R2 Standard.
The SQL memory usage of this server flatlines at around 1665mb and will not go any higher.
I understand SQL 2000 Standard on Windows 2003 Server Standard has a 2gb memory limit, however why wont the memory push up to this limit? The OS shows there is around 700mb of the 3076 unused (SQL uses 1660, windows uses around 700 making the maximum used around 2300 of the 3076)
I have set the /3gb switch on windows boot, I have changed the SQL memory configurations many different times. If I try and set 'min server memory' to anything over around 1500mb I get the error on SQL startup;
Warning: unable to allocate 'min server memory' of 1720mb
In the above example surely if the Server has a 2gb limit it should let me set say 1720/2048 ? or 1920/2048 or even 2048/2048, however anything over around 1500 generates the above error.
Our server continues to flatline at 1660mb, any help to squeeze out an extra 200-400mb of RAM would be appreciated or an explination as to why it can not be done.
Thanks for your help
Jim
Memory Settings In SQL Server 2005 Standard Edition
Nov 9, 2007Hi,
I'm having trouble finding the optimum memory settings for SQL Server 2005. I have 4 instances running on a macine with 8 dual-core processors and 18GB of RAM. I have tried the following memory settings so far:
No maximum - one instance used about 12GB of RAM so then the others struggled
Maximum of 4GB each (2GB left for Windows) - meant that 3 instances could be using 1GB each and then another at 4GB and needing more whilst 9GB sat unused.
Minimum of 2GB each - one instance would use up 10GB and then never give any back to the other instances.
I also find that setting a maximum then just causes a high amount of paging. What I would like to do is have each instance use a minimum amount, say 1GB, and then have each instance use a maximum of 13GB (3GB for other 3 instances running at a minimum level and 2GB for Windows). This 13GB should then be released and allocated to another instance when necessary, assuming it is no longer all being used. I do not want paging to occur if an instance reaches 13GB.
How do I go about configuring SQL Server to behave like this? Is it possible?
Thanks.
Lock Pages In Memory Option On SQL 2005 STD 64-bit With W2K3 R2 Standard 64-bit - Does It Work?
Nov 9, 2007 Hey Folks -
I'm setting up a new production SQL 2005 64-bit server running on a dedicated Windows 2003 R2 Standard system w/8GB of RAM.
I've enabled the "Lock Pages in Memory" option (http://msdn2.microsoft.com/en-us/library/ms190730.aspx) but when I run dbcc memorystatus all of my AWE counters show 0.
According to http://blogs.msdn.com/sqlprogrammability/:
"On 64 bit machines, execute 'dbcc memorystatus'. If the AWE Allocated memory is 0 then lock pages in memory privilege has not been granted to the service account or it has not taken effect. "
Does the "Lock Pages in Memory" option work on W2K3 R2 Standard? Just searching across the web I find different answers. In some places it clearly says that Lock Pages in Memory only works on W2K3 Enterprise systems. In other places it says that it should work on any Windows 2003 64-bit OS.
Does anyone have any ideas? Assuming we cannot "lock pages in memory" due to the Windows 2003 Standard OS, are there any other memory management recommendations out there?
thanks!
Setting Memory Values For SQL 2005 Standard (32 Bit) On Windows 2003 Server 64 Bit.
Aug 3, 2007Hi,
I am testing SQL 2005 Standard (32 bit) on a Windows 2003 Server 64 bit with 8GB of RAM? Should I enable the AWP Setting or not and should I change the maximum server memory (currently saying 2GB)?
Thanks!
Tom
SQL Server 2014 :: Capturing Min Max And Avg Of All Numeric Columns Within A Database
Jul 1, 2015I'm trying to capture Column Statistics Profile as if I was using SSIS data profiling task. I do not have this option and would like to see how I could go about capturing the min max and avg of all numeric columns within a database.
View 0 Replies View RelatedUpgrade SQL Express Database To SQL Server Standard Database
Dec 6, 2006Can I upgrade SQL express database to SQL server Standard database? Please give information to me! Thank you!
View 4 Replies View RelatedCompare Standard Evalulation To Standard License Version
May 30, 2007Hi All,
Good Evening.
I need a comparision between Evaluation copy and Standard license version of SQL Server 2005 /SSIS.
I'm assigned a task to evaluate SSIS and migrate a project to SSIS.
I have downloaded SSIS evaluation version and started working on that.
Now i'm being posed questions for the complete functionality of the tool...
- Whether it has a Bulk load trasformation ?
- Whether the evaluation version contain all the features of a standard license version ?
I need to submit a consolidated report for SSIS in comparision to the current ETL tool features.
Can you please let me is there any considerable features not given with a evaluation copy ?
Thanks in advance,
Suresh N
Standard Edition Database Snapshots?
Apr 16, 2007Hi. I'm evaluating performance features between Enterprise Edition and Standard Edition. I've heard that Database Snapshots are only available in Enterprise Edition, but cannot find anything that proves/disproves this.
Does anyone currently use Database Snapshots in Standard Edition SQL Server 2005? (Not talking about the Replication Database Snapshots.)
Quick script to try on a Standard Edition SQL Server:
CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
Thanks!
Using MAX, Then Capturing All Except Max
Sep 26, 2005I need to first capture the max version associated with a file name here is the meat
SELECT
fileName,
f.folderName,
masterID,
iterationId,
iterationNumber version
FROM
fileIteration fi
JOIN iteration i on (i.iterationID = fi.fileIterationID)
JOIN vw_folderFileLink fl on (fl.fileMasterID = i.masterID)
JOIN folder f on (f.folderID = fl.folderID)
WHERE
fileName LIKE '429020652.idw'
--GROUP BY masterID, fi.FileName, f.FolderName, fileName, i.iterationID
ORDER BY masterID, version DESC
--*******end code ********--
that returns these results:
429020652.idwSubPumps71575217
429020652.idwSubPumps71575116
429020652.idwSubPumps71574975
429020652.idwSubPumps71574654
429020652.idwSubPumps71573983
429020652.idwSubPumps71573142
429020652.idwSubPumps71573131
First I need help just getting that top row but then I need to get all expect the top row selected.. I've tryed using MAX in several ways but I'm not getting what i want here :/ any help is appreciated
7.0 Standard Desktop Database Size Limitation?
Nov 5, 1999I am running 7.0 standard desktop on Workstation 4.0 and have run into a database size limitation of 10GB. I have all the newest
patches and have found no documentation that refers to a 10GB limitation. A September copy of VBA developer says that this edition
has a limitation of 4GB while the guides say that the Small Business Server edition would have a 10GB limitation. All other forms
of documentation say that there should be the standard mega-terabyte limit. I'm completely confused by this [lack of] documentation.
Any help???
Unable To Attach DataBase In SQL Server 2005 Standard
May 6, 2007Now that everyone helped me install SQL Server, it's time to use it... then...
I'm trying to work my through the MS Press book ADO.Net Step by Step. After installing the practice files, there is a step to attach the sample database provided. Within the folder which contains the sample files, I'm told to execute an app which I believe was called "Config.exe". This app brings up a form with 3 text-boxes, to collect the Server Name, Database Name, as well as the Database MDF Filename. There are also 2 buttons, "Attach" and "Detach".
This book assumes, that SQL Server Express is installed. In the book, the author does mention that if this isn't the case, to adjust the Server Name as necessary.
(The following do not include the quotes, but you probably already knew that...)
In the form mentioned above, the default Server Name is "(local)SQLEXPRESS",
I changed this to "(local)MSSQLSERVER".
The next text-box for Database Name contains: "AdoStepbyStep"
The last text-box for Database MDF Filename contains: "c:ADO.NET 2.0 SBSSampleDBsAdoStepbyStep_Data.MDF"
The path/spelling of the text in the above is indeed correct. Clicking the "Attach" button, results in the following:
--- ERROR ---
Function: AttachDB
Description: [Microsoft][ODBC SQL Server Driver][DBMSLPCN]InvalidConnection.
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionOpen (ParseConnectParams()).
I've never used SQL Server, so please any advice needs to be at a relatively elementary level. I've primarily been a mainframe programmer, so this is new to me.
Thanxxxxxxxxxxxxx in advance for any replies!!!
Attaching A SQL Express Database To SQL Server Standard Instance...
Oct 18, 2006If I create a database in SQL Express and then copy the MDF/LDF files and attach them to a machine running SQL Server 2005 Standard - will this new database have SQL Express limitations?
Are there good reasons not to do this and create the database in SQL Server Standard using scripts generated from the SQL Express instance instead?
Basically, I'm setting up a merge replication between Standard and SQL Express for a common database and I figure attaching a copy of the database created in SQL Express is the fastest way to get this started.
Capturing A Variable In ASP.NET
Aug 12, 2004Hi all,
I am new to .NET, after many years with classic ASP I am struggling a little with something that I am sure is really easy to do.
Basically what I want to do, is execute an SQL statement, that will return a single value. I then need to store this value as a variable, so that I can pass it into another query later.
It seems easy to output the record, but how do I store it as a variable !!
This is my code to connect and run the SQL... all I need to do as retreive the value, and put it aganist a variable....
Function higher_manager_ein() As System.Data.IDataReader
Dim connectionString As String = "server='myserver'; user id='userid'; password='pwd'; database='DB'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT distinct MEASURE FROM [CCC_MEASURE] where ein = '" & request("man_ein2") & "'"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Capturing Queries
May 22, 2000Is there any way(other than Profiler) to capture a full SQL Query statment. I've used dbcc inputbuffer and the current activity screen, both return 255 characters. I'd like to capture the whole query. Thanks
Pete Karhatsu
Capturing Server Name?
Sep 20, 2004Hi y'all....long time, I know...
I have what OUGHT to be a simple question...or so I think (which really hurts, mind you...)
I am writing a stored proc that will reside on several different databases and be used to write a row to a "wait table" that is used to control processing in the various databases...
Essentially, the stored proc already exists, and writes what is essentially a note (or process semaphore) that says "Hey, Process XYZ is waiting on the completion of process 123"
Problem is...process XYZ has the same name on a number of different servers, so I have to come up with a way to differentiate the process name that's waiting on a job on a single server (in other words, 5 or more XYZ's can be waiting on a single job on a single server in the network, and the wait table resides on that single server).
So...my thought (again, think pain) is that I will put a process name of "SERVER.XYZ" into the wait table.
The SP I will use to write the "waiting on" semaphore is a common one, so - long story short(er) I need a way to capture the name of the current server (like db_name(), only server_name() - or something like it).
Any suggestions? Thanks in advance...
Paul
DMV's For Capturing CPU Utilization
Jun 3, 2008Hi
Please let me know which DMV is best to cpature total system CPU utilization. There are plenty of views so i am a lil bit confused
thanks in advance,
How Many GB Of Memory Does 1 Database Take
Nov 6, 2013I need to take each database off of a physical instance and give memory, cpu specs of what each database needs to run on the VM.
If I have 10 databases on one instance (SQL Server 2005 Standard) and need the memory used by one database, if I run the following query...
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Total Pages';
[Code] ....
(Found here [URL] ....)
Is db_buffer_percent the percentage of the max server memory set for the instance?
(db_buffer_percent of DB) * (Max server memory) = Amount of memory needed for DB
Is this accurate or at least accurate enough.
Capturing ID When Executing An INSERT
Jan 2, 2008I have a bit of code that executes an INSERT statement to add a record to an existing table. This table of course has an automatically incrementing ID field, and I'd like to somehow have my INSERT statement return the value of that ID field so that I can automatically show the user the record they've added.
Is there a clean way to do this?
Capturing Value Of Identity Column For Use Later?
Jul 20, 2005This doesn't work because the first INSERT is creating multiplerecords for multiple projects. @@IDENTITY, then, contains the Identitycolumn value for the last tblWeekReportedLine record inserted.Consequently, all the hours records are then associated withthat last value.The source work table, #EstimateLines, is a pivoted representationwith a Begin/End date and some Hours for each of six periods - a lineper project that gets pushed up to the DB by some VB code.Definition below the sample coding.The "@WeekReportedID" value was successfully captured whenprevious coding inserted six records into that table: one foreach date range (i.e. column in the UI screen)Sounds like I'm approaching this wrong.Suggestions on the right way to go about it?---------------------INSERT INTO tblWeekReportedLine(WeekReportedID,RelativeLineNumber,ProjectID)SELECT@WeekReportedID1,#EstimateLines.RelativeLineNumber,#EstimateLines.ProjectIDFROM#EstimateLines;SET@CurWeekReportedLineID = @@IDENTITY;INSERT INTO tblHour(WeekReportedID,WeekReportedLineID,HoursDate,Hours,HoursTypeID,HoursType,TaxCodeID,TaxCode)SELECT@WeekReportedID1,@CurWeekReportedLineID,@BeginDate1,Estimate1,@DummyHoursTypeID,@DummyHoursType,@DummyTaxCodeID,@DummyTaxCodeFROM#EstimateLines;------------------------The #Temp table create via VB:------------------------1030 .CommandText = "CREATE TABLE #EstimateLines " & _" ( " & _" PersonID int, " & _" ProjectID int, " & _" RelativeLineNumber int, " & _" Available1 decimal(5,2) Default 0, Estimate1decimal(5,2) Default 0, BeginDate1 DateTime, EndDate1 DateTime, " & _" Available2 decimal(5,2) Default 0, Estimate2decimal(5,2) Default 0, BeginDate2 DateTime, EndDate2 DateTime, " & _" Available3 decimal(5,2) Default 0, Estimate3decimal(5,2) Default 0, BeginDate3 DateTime, EndDate3 DateTime, " & _" Available4 decimal(5,2) Default 0, Estimate4decimal(5,2) Default 0, BeginDate4 DateTime, EndDate4 DateTime, " & _" Available5 decimal(5,2) Default 0, Estimate5decimal(5,2) Default 0, BeginDate5 DateTime, EndDate5 DateTime, " & _" Available6 decimal(5,2) Default 0, Estimate6decimal(5,2) Default 0, BeginDate6 DateTime, EndDate6 DateTime, " & _" );"--------------------------PeteCresswell
View 2 Replies View RelatedCapturing Events In A DataGrid
Jul 20, 2005I'm trying to add functionality to a VB 6 application allowingcustomer service to add a customer number to a new customer.Customers are added to the database by sales personnel, and aprospective customer may have multiple rows due to projected ordersfor multiple products. Customer numbers are assigned when a newcustomer makes their first order.I'm using a DataGrid connected to an ADO Data Control. The datacontrol is connected to a view in SQL Server using the 'distinct'directive (I know it's not updatable) to show only one line per newcustomer. What I wish to do is capture the update event (probablythrough the FieldChangeComplete routine of the data control), manuallyassign the newly entered customer number to all appropriate rows inthe database, and cancel the update event with no notifications.I'm having two problems:1. I can't capture the newly entered customer number. The Textproperty of the DataGrid returns the old value of the cell instead ofthe newly entered value. How do I get the edited value?2. Even though I set adStatus = adStatusCancel in theFieldChangeComplete routine, I get a Microsoft DataGrid Control dialogstating 'Operation was Canceled'. How do I avoid this notification?
View 1 Replies View RelatedCapturing Execution Results
Nov 13, 2006Hi All
When running an SSIS Package from BIDS, we get to see the "Progress" tab which explains us the progress of the SSIS Package. During production, is there any way to capture this log. I am interested in using this as a log file for each run of my SSIS package.
Thanks,
S Suresh
Capturing Component Throughput
Dec 19, 2007[Microsoft follow-up]
I've just been reading this thread by a guy asking about capturing the throughput of a dataflow. I suggested that there is no real notion of capturing throughput of a dataflow but I believe there IS a notion of capturing the throughput of a component or an execution tree.
I believe all the information that one would need to capture the throughput of a component (apart from the name of the component that is) is available in the OnPipelineRowsSent event. If there were a OnPipelineRowsSent eventhandler and the OnPipelineRowsSent event contained the name of the component then I think we would be able to capture the throughput of a component. So, some questions:
Why is there no eventhandler for the OnPipelineRowsSent event?
Can the name of the component be added to the information in the OnPipelineRowsSent event?
Following on from this... I once had a conversation here with Kirk Haselden about capturing pipeline throughput. He thought it was a good idea and suggested I raised a DCR for it which I did but that was in the old pre-Connect days and it seems as though that DCR (like SO many other things) didn't make it across to Connect. So, some more questions:
Can you find any Connect DCRs relating to capturing throughput? I've found this: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=152162 that I raised 18 months ago but which hasn't even had a single comment from anyone at Microsoft.
Do you think that capturing throughput would be useful? I can foresee huge advantages by capturing this in the debugger. (Note that Informatica does this and has done for years. It has a very nice GUI that shows the throughput of each destination in the mapplet.)
I'd welcome any thoughts around this. Its a big ask and it fits in very nicely with my constant, nay INCESSENT, requests for debugging enhancements so maybe this is one for Darvey to have a read of???
Thanks
Jamie
Capturing One Record In A Series
Nov 30, 2007
My question is this.....
I inherited an application that creates reports and each report pulls data from a SQL database. The reports are for each School district in the state and pull the number of students that have Asthma conditions, simple enough. However what happens is this... Each initial report is saved with a code of 2 for initial. As long as there aren't and revisions I am fine. If a school makes a revision (Code of 4) I now have two records in the database and I need the one from the revision (Code 4). Not every record will have a revision, and sometimes there may be multiple revisions. How can I code my stored procedure to go through the database and check to see if there is a code of 4 or multiple codes of 4 and if so grab that data, if not grab the initial data? Below is the code currently being used. Frank
CREATE PROCEDURE dbo.usp_Rpt_Asthma
(@WhereClause VARCHAR(400))
AS
SET NOCOUNT ON
DECLARE @WhereClause_STR VARCHAR(400)
SELECT @WhereClause_STR = CONVERT(VARCHAR(400),@WhereClause)
Exec ('Select D.Code, C.Code, InstCtgyFK, C.[Description] as County, D.DistrictName, A.InstName,
Sum(K4+K+G1+G2+G3+G4+G5+G6+G7+G8+G9+G10+G11+G12+UnGrSpEd+Other) as TotalADM, Asthma, Asthmatics
FROM dbo.tblAnnualReports AR
Inner Join dbo.tblAddresses A on A.InstitutionFK = AR.InstitutionFK
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblInstitution] I on A.InstitutionFK = InstitutionPK
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblActiveInstCtgy] AIC on A.InstitutionFK = AIC.InstitutionFK
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblCounties (PA Standard)] C on C.Code = I.DOHCountyCode
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblCommunityHealthDistricts] D on D.Code = C.CommunityHealthDistrictCode
Inner Join dbo.tblAverageDailyMemberships ADM on AR.[ID] = ADM.AnnualReportID
Inner Join dbo.tblChronicConditionsInjuries CC on AR.[ID] = CC.AnnualReportID
Inner Join dbo.tblMedicationAdministration MA on AR.[ID] = MA.AnnualReportID
Where (A.StartDate <= AR.DOHDateProcessed and (A.EndDate >= AR.DOHDateProcessed or A.EndDate is Null or A.EndDate = ''1/1/1900''))
and ReportTypeCode = 2
and IndOrdStandOrd = ''I''
'
+ @WhereClause_STR +
'
Group By D.Code, C.Code, InstCtgyFK, C.[Description], D.DistrictName, A.InstName,
Asthma, Asthmatics
')
GO
In-memory Database In SQL Server 64-bit
Jan 15, 2007Hi,
I am sorry if this question has been asked before, but I don't seem to be able to find any online documentation on it.
1) Do all 64-bit versions of SQL server allow database tables to reside in memory?
2) How do I enable this feature of having my tables reside in-memory?
3) Is there a way to choose which tables are in-memory and which reside on disk?
Any tips or documentation on this "in-memory" feature would be appreciated.
Ivan
SQL2005 Enterprise Database Engine Performance Features (versus Standard)
Feb 21, 2007Can anyone comment on the engine performance difference between SQL2005 Enterprise Edition versus Standard? I'm talking generalized performance of the engine and not admin features (parallel index operations) or scaled-storage (partitioning)
( http://www.microsoft.com/sql/editions/enterprise/comparison.mspx )
The marketing literature makes note of two things:
Enterprise can use more then 4 processors
Enhanced read-ahead and scan (super scan)
(note: I cannot find anything about this 'feature')
One un-noted Feature:
only Enterprise supports 'lock pages in memory'
We are in the process of migrating from SQL2000 to SQL2005 in an OLTP environment. Based on the marketing literature; I would have chosen SQL2005-standard. But based on our limited testing, we are seeing some strange differences.
Query Performance
With MaxDOP=1 and using a large batch query (select top 1500000); SQL2005-Enterprise is twice as fast as SQL2005-Standard.
(Note: this difference persists regardless of lock-pages-in-memory setting)
CPU Utilization
In addition, taskmgr shows that SQL2005-Enterprise uses a single processor at ~90%. While SQL2005-Standard shows a single processor at ~20%.
Lock Behavior
We are also seeing lock-behavior differences. A single DML statement that attempts to modify ~5000 rows will cause Table-locks on SQL2005-Standard but obtain normal row-locks on SQL2005-Enterprise.
These empirical differences make me wonder if the engine codebase is fundamentally different between the two?
Any insight would be appreciated.
Proc For Capturing Date And Time
Aug 31, 2007I'm creating a sproc to to count the dates and times users log on to a page. I want the date and time broken out into two columns.
When I attempt create the sproc I get the following error:
Subqueries are not allowed in this context. Only scalar expressions are allowed.Create PROCEDURE [dbo].[LoginCounter]
@Username Nvarchar(50),
@IPAddress Nvarchar(50),
@BuilderID, INT
As
INSERT INTO LoginCount ([UserName], [IPAddress], [Date], [Time], [BuilderID])
VALUES (@Username, @IPAddress,
(SELECT CONVERT(VarChar(2), MONTH(GETDATE()))+ '/'+ CONVERT(VarChar(2), DAY(GETDATE()))+ '/'+ CONVERT(VarChar(4), YEAR(GETDATE()))),
(SELECT Convert (varchar (2), DATEPART(hour, GETDATE())+ 3) +':'+ CONVERT(VarChar(2), DATEPART(minute, GETDATE()))),
@BuilderID)
Capturing SQL Queries Of 3rd Party Application
Sep 15, 2003Hello.
I would like to analyze how a particular 3rd party business application interacts with SQL server.
Specifically, I want to capture the "exact" SQL commands (transact-sql statements) that this application issues whenever it completes an operation for the user of this application.
In other words, suppose the application issues the SQL command "INSERT INTO table (a,b,c) values (1,2,3)" to add a new entry to the database. Then, my understanding is that if I instead issue the exact same command with a tool like "SQL query analyzer", the database will be updated in the exact same manner. Hence, I have effectively accomplished the same job that the 3rd party application does without even using it (by idependantly issuing the same command to the database it uses).
Is this possible? Can I obtain all the information I need just by running SQL Profiler?