I would like to know if there is a penalty for Varchar variables in stored procedures if I declare them Varchar(8000) instead of Varchar(1000).
I have a lot of variables and sometimes the content will be more them 1000 characters.
Is memory only allocated for the the actual contents or for the complete declared length?
I have around 100 packages (all [packages run at same time) each package calls a stored Procedure once the Stored proc Execution is completed the package will write the log information into a log table, here how can i capture the CPU and Memory usage for execution of each stored proc.
View 2 Replies View RelatedHow do you declare and then SELECT a value for a local variable withinstored procedure, increment the value and then use in an Insertstatement? ThanksAny sites that explain this syntax for SQL Server 2000? Thankshals_leftCREATE PROCEDURE [dbo].[InsertQualUnit]@QualRef tinyint,@UnitRef tinyint,@UnitGroupRef tinyint,// this needs to be a local var not an output param, how ?@UnitPosition tinyint OutputAS// Assign a value to the the variable from a SELECT query, how ?SELECT @UnitPosition= SELECT MAX(UnitPosition) FROM tblUnitGroupWHERE QualRef=@QualRef AND UnitRef=@UnitRef ANDUnitGroupRef=@UnitGroupRef// inc the value@UnitPosition+=1// Use the new value in another SQL statementINSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,UnitPosition )VALUES ( @QualRef, @UnitRef, @UnitGroupRef , @UnitPosition)GO
View 4 Replies View RelatedHi there,
Could any one suggest a query which yields the list of tables and columns used in a stored procedures. I know sp_depends has similar functionality, but would like to know the T-SQL code for that..
Madhu
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Can anybody help me with this?
I have a stored procedure where I gather some data and then insert the data into a table variable. I then attempt to go through each row of the table variable, asign the values to local variables to be inserted into other tables. However, the local variables show as NULL.BEGIN
DECLARE @tblcontact table
(
SOKey int,
Cntctkey varchar(60),
Cntctownerkey int,
LASTNAME varchar(32),
FIRSTNAME varchar(32),
WORKPHONE varchar(32),
EMAIL varchar(128),
processed int DEFAULT 0
)
INSERT INTO @tblcontact (SOKey, Cntctkey, Cntctownerkey, LASTNAME, FIRSTNAME, WORKPHONE, EMAIL)
SELECT ...
DECLARE @ID int,
@sokey int,
@cntctkey int,
@cntctownerkey int,
@name varchar(65),
@email varchar(128),
@phone varchar(32)
WHILE EXISTS (SELECT * FROM @tblcontact WHERE processed = 0)
BEGIN
SELECT @ID = MIN(SOKey) FROM @tblcontact WHERE processed = 0
SELECT @cntctkey = (CAST(LTRIM(REPLACE(Cntctkey,'CN',' '))AS int)),@cntctownerkey = Cntctownerkey, @name = FIRSTNAME + ' ' + LASTNAME, @phone = WORKPHONE, @email = EMAIL, @sokey = SOKey
FROM @tblcontact
WHERE @ID = SOKey AND @cntctkey <> '43778'
INSERT INTO tciContact (Cntctkey, Cntctownerkey, CreateType, EMailAddr, EmailFormat, EntityType, ExtUser, Name, Phone, UpdateCounter)
VALUES (@cntctkey, @cntctownerkey, '0', @email, '3', '401', '0', @name, @phone, '0')
UPDATE tsoSalesOrder
SET Cntctkey = @cntctkey, UserFld4 = 'temp'
WHERE SOKey = @sokey
UPDATE @tblcontact
SET processed = 1 WHERE @ID = SOKey
END
END
I am brand spankin new to stored procedures and don't even know if what I want to do is possible. From everything I've read it seems like it will be. I have a table, punchcards. In this table are all the punch in/out times for a week. I want to create a stored proc to calculate how many hours a punchcard entry is.
Thats the dream.
The reality is that I can't even get a tinyint from a table to load to a variable and be printed out. I am using sql server 8.
Here is what I have as of this moment for my sp.
ALTER PROCEDURE usp_CalculatePunchcard
AS
DECLARE @dtPP DateTime
SET @dtPP = (SELECT thursday_in1
FROM punchcards
WHERE (punchcard_id = 1))
/*
Also tried....
SELECT @dtPP=thursday_in1
FROM punchcards
WHERE (punchcard_id = 1)
*/
PRINT @dtPP
RETURN
/*
for some reason i can't use GO ... even though every
document i've read on stored procedures has used GO
and none use RETURN
*/
The only output this is producing is ' Running dbo."usp_CalculatePunchcard". '
Any help would be greatly appreciated as I am about to kick someone/something.
Thanks
I have a PROC that I want to modify via a variable I will pass in from ASP.Net.
Currently the PROC is as follows:
ALTER Procedure [dbo].[spu_FindProperties]
@Search varChar(30)
AS
Set @Search = '%'+ @Search + '%'
SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1,
dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP,
dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWO
FROM dbo.EDCclient INNER JOIN
dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID
WHERE (dbo.EDCclient.CLIENT like @Search) OR
(dbo.EDCproperty.PROPERTY like @Search)
I now want to modify it so that the entire Where clause is created in ASp and passed in. So I think my new PROC should look as follows:
ALTER Procedure [dbo].[spu_FindProperties]
@Search varChar(200)
AS
SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1,
dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP,
dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWO
FROM dbo.EDCclient INNER JOIN
dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID
@Search)
But I can't get SQL to accept the @Search in place of the Where... Any ideas?
I'm working on my first stored procedure, having just watched a video about how to use them. I have a working procedure that looks like this:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[InMateSearch] @State nvarchar(255) AS BEGIN DECLARE @SqlString nvarchar(255) SELECT [FIRST NAME], [LAST NAME], [STATE] FROM members WHERE suspend=0 AND (State=@State OR Country=@State)ENDWhat I need to do is build the WHERE clause of the SELECT Statement based on the values of the parameters passed in. There will be a lot of them, but State will do for an example.I want to assign a variable, say SqlString = " suspend=0". Then I want to check the value of @State which is a parameter being passed in, then do something like this:If @State <> "" then SqlString = SqlString & " AND (State=@State OR Country=@State)" so that I'm only filtering by state if the state parameter isn't empty. Then I would like my SELECT statement to look like: SELECT [FIRST NAME], [LAST NAME], [STATE] FROM members WHERE (SqlString)I'm declaring SqlString as the video indicated, but in the video the variable is assigned a value from the database. I think I need a different kind of variable. If I try to do something like SqlString = "test" or @SqlString = "test" I get an error. Once I define or declare a variable I can use to build the string, how do I assign it to the WHERE clause?Diane
View 3 Replies View RelatedHi,
What is the syntax calling a stored procedure (let say named 'myproc') with OUTPUT variables (lets say the proc has an integer output variable named 'myvar') ?
Thanks,
Dror.
I've got a number of stored procedures that I have for reporting
All are of a similar starting format
For easier maintenance and to take away the need to change all of them if the methodology changes I want to split out shared code.
What I want to do is to take out the part that populates the @ID1 table into a separate stored proc which will be called from the report procs. The values from the shared proc will then be parsed back to the reporting proc.
I thought about using a function but I don't think it will be flexible enough as in certain cases I want to parse 2 or more IDs back into the final output.
I also don't want to make the code too complex so that it is relatively easy to read
CREATE PROC dbo.ReportM1 @ID INT AS
DECLARE
@ID1 TABLE (ID INT PRIMARY KEY, UNIQUE(ID))
IF @ID = 0
INSERT INTO @ID1
[Code] ....
The first question I have is: can i do it with a table variable when going between procs or do i need to build a real table if i want it to maintain the logic in 1 place.
May be worth bearing in mind that the end user who will be executing the proc will only have read + execute stored proc access permissions so dropping, updating or creating real tables is not an option. #Temp tables are possible but since am using table variables throughout would prefer to stick with them.
Hi There,
I would like to know if for example i have some tables in my DataBase and i need from each table to get for instance, the Name by the ID, how can i make only one procedure which use parameters and variables so i can use this SP to get the Name By ID for all the Tables?
Thanks
I have a Windows sever 2012 with sql server 2012 enterprise. Ram size is 22GB. Sometimes SQL sever takes 95% memory.My question, How to reduce memory size without killing any process because it's production server.So there are many background process is running. And,Is there any guides to learn why Memory is raise d so high and how to reduce it.
View 10 Replies View Related
I have been looking at the project Real reference implementation for doing auditing of data uploads. The tables and store procedures are in place using identical field and variable names. However, when running the package it does not update the relevant log entry with the end time and status.
I have performed the process manually running the stored procedures, providing the values directly and everything works fine.
I can only assume that the LogID variable is not being updated during the run and therefore the onEnd procedure cannot update the relevant log entry.
One side effect is that it does update the record when it€™s the first record entered into the table but not on any other inserts. Clearing the table each time is not an option.
This has become rather frustrating and would appreciate any assistance.
Thanks.
i want to create a lot of index for my database for performance.but i need find memory usage by indexes.
How to find memory usage by index in sql server?
providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
Good day to all,
I'm new here, so I don't know if this is the right forum to post my problem. I have a web application written using C# .net 2005 (W/ajax). The application has a module that uploads data from excel file to the sql server 2005 database. w/c is by the way, i'm using SQL 2005 Express Edition, the app can upload up to more than 10,000 records from an excel file. Everything is ok until it was deployed in a test environment, while having a run through with the system, the application encounter an error after which, we cannot log in to the system anymore. I restarted the server (web and sql server in 1 machine running winxp) then I can log-in again in the system. When I'm tracing where the problem came from, I noticed that the memory usage of sqlservr.exe increases everytime the app connects to the server. I already fix some code to close some objects that might have caused the high memory usage, then I run sp_who in the management studio and there are still connections used by the app AWAITING COMMAND. Then I manually kill (using kill spid) connection that are left opened by the application. But the mem usage of sqlservr did no decrease. Is there a way to release the memory usage of sqlservr.exe? In ASP.Net ? I have a hint that this has been causing the error. Thanks a lot.
Hi,
it seems that every day SQL Server 2000 has some kind of memory leak,
the memory usage creeps above 150000 approximately 3 times per day. Is this normal? It starts at about 13000.
Is there any way that I can monitor what is causing the memory usage to be so high and maybe rectify it?
Thanks for the help.
Steve
I was on my friend's server earlier and I checked his system stats. Between sqlservr.exe and msftesql.exe he was using 2.9gb of ram. This is not a high traffic server so I felt that was just a wee bit high. What are some usage experiences you have seen? I have not worked with 2005 so I don't know what the usage should be like for a low to medium usage server. The guy that admins the db is kinda a moron anyway. He said he has said to the server admin it has been optimized, but I dont believe it one bit at 2.9gb of usage. Any comments would help.
Pete
Hello,
I need to know which of the following two methods do need less RAM.
There are 2 big tables, each about 9 M rows, and 6 small dimension tables with each about 10 to 100 Rows. The dimension tables are joined by their id's with one of the big table.
The Structure of a dimension Table looks like
CarID (tinyint), Description (varchar(20))
1 BMW
2 Porsche
I want to join the 2 Big Tables in a materialized view. Later i will run queries like
select * into #temp from dbo.vw_materialized_view where Car = 'BMW'
So, back to my question, will such a query take less memory (ram) when i joined all 8 tables before I created the mat. view or will it take less when I only join the 2 big tables in a mat.view and later join the mat.view with the 6 dimension tables?
Hope you got that ;-)
Thank you
Can some one tell me where can I get the CPU and Memory Usage of my server on the Sql Tables or a script or store proc that i could run to get the above. I need that and to store its results in history to show how are our servers perfoming
View 8 Replies View RelatedHello, I have SQL Server 2005 64 bit on Windows Server 2003 Enterprise 64 bit on a server with 8GB of RAM
Task Manager reports the process sqlserver.exe consuming 7GB of memory
Is this normal ?
how to check the memory usage other than performance monitor for the server?
When I execute the SP_WHO2 on the server, it showing each user have 5 connections there and the status is sleep. what is that means?
Does it takes up memory?
why have so many connection or entries for the same user, they all are using the same db with same application.
Thanks!
I have setup 2 x64 SQL boxes now that are having problems with memory. Both boxes were pretty much setup with the defaults on windows 2003 R2 64 bit server, only changes were file locations etc.
On both servers the memory usage continues to climb over the course of a few days until all physical and virtual memory is used and the server comes to it's knees. A reboot will stabilize it for a few days while the memory usage ramps up again.
The sqlservr.exe process does not report much memory but the commit charge in the performance tab of task manager shows the maxed out value. I thought the sqlservr.exe process only didn't report the correct memory figure when AWE is enabled which it isn't.
I thought as a default, SQL server would dynamically allocate memory as needed and had internal mechanisms that would not use more than the physical ram available. The one server has 16GB and I even set the max SQL memory usage to 12GB but still my total memory usage would get out of hand. SQL Server is the only application on these boxes.
Am I making some incorrect assumptions and need to change the way SQL is setup? Any suggestions?
Thanks
Scott
I'm trying to get the machine free ram value using either T-SQL orSQL-DMO.I found .Registry.PhysicalMemory which takes care of the total ram butI still need eithrt the free or the used.Any ideas?Thanks!
View 1 Replies View RelatedHi All -
We have some CLR sprocs and tvf's we run in a batch job that recently have been getting the out of memory issue. I want to increase the amount of memory allocated to the CLR using the -g startup switch but i want to make an intelligent decision on how much to allocate. What are some of the best ways that you have found to estimate how much to give the CLR?
Thanks,
Cameron
How much available memory is best practice for SQL servers? We have some alerts set up by our system admin, they go off if the available memory goes below 500mb. Which is fine for other servers but I feel for SQL server it isn't quite correct. Currently we stand below at about 475mb and the PF usage is around 7gb.
It is SQL 2005 standard SP1 x64
dual intel xeon 5160s
8gb physical memory
buffer hit cache is > 99%
I am just not confident enough to make a solid decision here that this is acceptable. In the beginning of this month I ran some counters for 24hrs to get a feel for the baseline. The average for available memory was about 850mb at that time. So I am wondering if it is going to keep declining and turn into a problem. The server/instance has not been restarted since maybe last November.
thanks
Does anybody know why BCP on v6.5 grabs so much memory for SQL Server? I have a few table imports where the BCP process will consume over 460MB of RAM during the imports.
The BCP cmd file is executed via an xp_cmdshell call. The server has 2+GB of RAM, but the BCP process effectively flushes large amounts of data from the buffer. It takes quite along time for the cache to recover from this, and after this, the rest of the nightly processes run much slower, as they end up having to hit the drives to retrieve information that should already be in cache.
If anyone can shed some light on this it would be much appreciated.
Ian Dundas
DBA
Assante Asset Management
Hi,
One of the production box running only sql server application, is showing 80% memory usage on the task manager-memory usage history right now.
We are running sql server 2000 standard version-sp3 with 2GB memory on this box. Server is not on the scheduled reboot at this point.
We have seen this behavior for this box last month that after task manager showing 90% memory usage contantly for several days, when server was manually rebooted, memory usage dropped to 35%. Now it's back to 80%.
Our DBA thinks that server should be rebooted on a regular schedule regardless of memory problem. Our network admin doesn't seem to agree with this. He is not ready to reboot the machine even with this high memory usage.
There is no noticable difference performancewise yet.
My questions are:
Is it bad that memory usage reaches from 35% constant to 80-90% or is it common? Should sql server be rebooted immediately to take care of it? Should sql server 2000 rebooted on regular basis regardless of any problems? Shouldn't sql server be releasing memory back to the OS even without rebooting? How do I find out whether server actually is going through memory problems and what is causing it?
Thanks in advance for your opinions,
Shaili
hi all
I got a small MSSQL server.. total database file size less than 7GB. with 2G rams installed 2 cpus. but for some reason when i check the task manager process mem usage is over 1.7G. the sql server memory setting in on Dynamically not fixed. and maximum 2G i believe is default. anyway. my question is over 1.7 memory usage is too high? because i dont have alot of transaction going. and cpu usage is very low. wondering if this's normal or not. and if is not normal . what cause the memory usage so high...... and how can i adjust back to normal. ? can anyone help me out? or any suggestion? thanks
Hi All,
Currently I have an application that uses SQL 2000. The SQL server service tends to take up as much of the physical memory as possible. The problem is I also have other services relating to this application running that are very important.
What tends to happen after a period of time is SQL takes up all of the physical memory, so that the other services are using the paging file (virtual memory). This causes extremely slow response time over the network as these other services are having to parse the paging file.
Upgrading the memory is currently not an option :(
I know there is an option to set memory usage for SQL but I am unsure how this would respond in a production environment. What would happen if SQL would require more memory than what was allocated to it?
Can SQL release the memory and still act as normal?
Any input would be generously appreciated
Cheers!
I am using SQL Server 7.0 on a Windows NT machine. We have been having problems with SQL Server not releasing the memory after it has utilized it.
Currently it is configured to allow a max of 511MB memory (1024 is total on the machine). I had some advice that my best solution would be to reduce the Max Memory to a lower value (say 400 MB) to help reduce the problem.
Is this not counter-intuitive? Or is this the correct solution?
Thanks.