sql server performance is mostly driven my disk performance. creating tables named #anything creates the table in the "tempdb" i think, but it definitely does not store the table in "your" database where you are working.
some server admins will put tempdb on a different drive array, and the other system databases (master, model, msdb) on a second disk array, and finally all user-created databases go an a 3rd disk array. each disk array should be tuned differently for the different aspects of sql server that those system databases are used for.... logging requires fast writes....other system dbs require fast reads (so you get mirrored pair disks)... however...i have seen execution times for the same sproc vary widely depending on current server load at the time of execution.... also as tables are "touched" (CRUD methods) indexes and table statistics should be updated/rebuilt.(sometimes sql needs a kick to make this happen) is there anything else on the server that could be sucking disk time....things like a middle of day backup or txn log shipping ..basically anything on the same disk partition as your database that would cause disk head contention... on a single physical disk system it could be as simple as a file copy operation with a total size of a couple of hundred Mb. -----Original Message----- From: Discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] Behalf Of Mike Andrews Sent: Friday, September 21, 2007 11:51 AM To: [email protected] Subject: Re: [ADVANCED-DOTNET] Need help with a CLR stored procedure takes >26 seconds to execute... Yes, it's quite repeatable. And when I first built it, it was quite fast. It's not the initial JIT either. I'm really at my wits end on this one. I dumped the assembly and sprocs and recreated everything, but still the same results. On 9/21/07, Pardee, Roy <[EMAIL PROTECTED]> wrote: > > This is out of my depth probably, but--could any of this be spin-up time > where mssql is loading the .net framework into memory, JIT'ing/verifying > your sproc, etc.? IOW--does it take 27 seconds the second time you run > this code? > > -----Original Message----- > From: Discussion of advanced .NET topics. > [mailto:[EMAIL PROTECTED] On Behalf Of Mike Andrews > Sent: Thursday, September 20, 2007 2:53 PM > To: [email protected] > Subject: [ADVANCED-DOTNET] Need help with a CLR stored procedure takes > >26 seconds to execute... > > I'm having a problem with a CLR stored procedure with which I need some > assistance. > The problem is not the stored procedure itself, per se, but SQL Server. > > Here's what's happening: > > I have a regular T-SQL stored procedure that generates the necessary > data and stores in a #temp table. > I then call the CLR stored procedure to operate on the #temp table data. > It returns data. > The problem is that from the time that I issue the EXEC XXXX to the time > it returns is ~27000ms. However, the timing statements I put in the CLR > sp indicate only ~300ms. So, there's a ~26000ms discrepancy for which I > cannot account. I have no idea what is going on here. I tried deleting > the sp and then the assembly and then re-installing the assembly and the > stored procedures but to no avail. > When I add up all the statements, the whole procedure should take <1 > second which is more than acceptable for this instance, but alas it > won't return for ~27 seconds. > > Any suggestions or seen this behaviour before? > > Thanks, > Mike > > =================================== > This list is hosted by DevelopMentor(r) http://www.develop.com > > View archives and manage your subscription(s) at > http://discuss.develop.com > > =================================== > This list is hosted by DevelopMentor(r) http://www.develop.com > > View archives and manage your subscription(s) at > http://discuss.develop.com > =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
